Change Text to Upper- or Proper Case
When using a built-in Excel function is impractical, you can use a custom macro to change any existing text to uppercase or proper case.
Excel already has two functions that change text to uppercase (all caps), or proper case (capitalizing the first letter of every word). Let's say we have the words hong kong disneyland in cell A1. The existing functions that can be used to change text are:
Converts all text in cell A1 to uppercase (all caps), giving the result of
NEW YORK DISNEYLAND
Converts all text in cell A1 to proper case which would give the result
New York Disneyland
These functions work well when referring to cells that house the text, but there are many instances when using the worksheet function approach is not practical, such as with massive amounts of data. We can fix this with the use of some simple code that displays a message box asking if we would like to convert to uppercase or proper case.
Right-click on your sheet tab, select ViewCode (or press Alt/Option-F11), insert a module by selecting Insert → Module, and enter the following code:
Sub ConvertCase( ) Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub
Click the X at the top-right side of the Visual Basic editor to close and return to Excel, and then save your workbook.
The code uses the STrConv function to convert the text. The StrConv function can take many forms, so if you want to convert to lowercase, for instance, you can substitute either vbUpperCase or vbProperCase in the code with vbLowerCase or any of the alternatives in the following list:
Converts the string to uppercase characters
Converts the string to lowercase characters
Converts the first letter of every word in the string to uppercase
Converts narrow(single-byte) characters in the string to wide (doublebyte) characters
Converts wide (double-byte) characters in thestring to narrow (singlebyte) characters
Converts Hiragana characters in the string to Katakana characters
Converts Katakana characters in the string to Hiragana characters
Converts the string to Unicode using the default code page of the system (not available on the Macintosh)
Converts the string from Unicode to the default code page of the system (not available on the Macintosh)
See the Excel VBA help for specifics.
To run the macro, press Alt/Option-F8, select the macro name, and press Run.
The macro will run and display a message box that asks if you wish to change existing text to either uppercase or proper case, and depending on user selection the code will convert the text.
If you run the macro with only a single cell selected, it will work on the entire worksheet. If you run the macro with more than one cell selected, it will work on only your selection.