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:

=UPPER(A1)

Converts all text in cell A1 to uppercase (all caps), giving the result of
NEW YORK DISNEYLAND

=PROPER(A1)

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.

The Code

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:

vbUpperCase
Converts the string to uppercase characters
vbLowerCase
Converts the string to lowercase characters
vbProperCase
Converts the first letter of every word in the string to uppercase
vbWide
Converts narrow(single-byte) characters in the string to wide (doublebyte) characters
vbNarrow
Converts wide (double-byte) characters in thestring to narrow (singlebyte) characters
vbKatakana
Converts Hiragana characters in the string to Katakana characters
vbHiragana
Converts Katakana characters in the string to Hiragana characters
vbUnicode
Converts the string to Unicode using the default code page of the system (not available on the Macintosh)
vbFromUnicode
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.

[Contents] [Next Tutorial]

Change Text to Upper- or Proper Case

The Code

In this tutorial: