VBA & Macro Problems

Convert Excel Text to Uppercase with VBA or a Formula

Algirdas JasaitisAlgirdas Jasaitis Nov 14, 2026 868 views

Question details

The user wants to convert Excel text entries to uppercase rather than only changing their formatting.

Convert Excel Text to Uppercase with VBA or a Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains or receives lowercase text that needs to be stored as uppercase.
Observed behavior
Conditional formatting can style cells but cannot change the underlying characters.
Before you start

Identify the cells that should be converted and decide whether you need automatic conversion of new entries or a one-time conversion of existing text. For the VBA method, use a desktop version of Excel that supports VBA and enable macros only in trusted workbooks.

Solution 1Recommended

Use a worksheet VBA event to uppercase new entries

Use this method to automatically convert text entered or pasted into a specified range.

The event changes text values in A2:A1000 when those cells are edited. It skips formulas and non-text values and does not convert existing entries until they are edited.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic.

2
Open the worksheet module

In the Project Explorer, double-click the worksheet where entries should be converted. Paste the following code into that worksheet's code window, adjusting A2:A1000 if needed: Private Sub Worksheet_Change(ByVal Target As Range) Dim affected As Range Dim cell As Range Set affected = Intersect(Target, Me.Range("A2:A1000")) If affected Is Nothing Then Exit Sub On Error GoTo HandleError Application.EnableEvents = False For Each cell In affected.Cells If Not cell.HasFormula Then If VarType(cell.Value2) = vbString Then cell.Value2 = UCase$(cell.Value2) End If End If Next cell Application.EnableEvents = True Exit Sub HandleError: Application.EnableEvents = True MsgBox "Uppercase conversion failed: " & Err.Description, vbExclamation End Sub

3
Save and test

Select File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted for this trusted file, then enter lowercase text in the specified range to test it.

Use a worksheet VBA event to uppercase new entries
Existing worksheet events: A worksheet can have only one Worksheet_Change procedure. If one already exists, incorporate the conversion logic into it rather than adding a second procedure.
Convert text in WPS Spreadsheets

Uppercase Excel text with WPS Spreadsheets

Use the UPPER function in WPS Spreadsheets to convert text, then paste the results as values if you want to replace the original entries.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 2. Add the formula: Select an empty cell next to the text and enter =UPPER(A1), changing A1 to the appropriate source cell.
  3. 3. Fill the remaining rows: Drag the formula cell's fill handle down over the rows you want to convert.
  4. 4. Paste the converted values: Copy the results, select the original cells, and choose Paste Special > Values. Save the workbook when finished.
Convert lowercase text with the UPPER functionOpen and edit Microsoft Excel XLSX filesPaste uppercase results as valuesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Can conditional formatting turn lowercase text into uppercase?

No. Conditional formatting changes a cell's appearance, not its stored characters. Use UPPER or VBA to convert the text.

Will the worksheet macro convert text that is already in the cells?

No. The Worksheet_Change event runs when cells are edited. For existing text, use =UPPER(A1) in a helper column and paste the results back as values.

Why does the VBA code need to be in the worksheet module?

Worksheet_Change is a worksheet event. In Developer > Visual Basic, open the target worksheet in Project Explorer and place the procedure in its code window.

How do I keep the VBA code when saving the workbook?

Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). Enable macros only if you trust the workbook.