Changing Text Case

This page describes changing data to upper, lower, and proper case.
ShortFadeBar

Excel has no formatting capability to display text in all upper or all lower case. If you need your data to be in upper, lower, or proper case, you must either convert your existing data, change the case upon text entry, or allow only upper or lower case data to be entered into a range.

DEFINITION: Proper Case
Proper case text is text in which the first letter of each word in capitalized. For example, "This Is Proper Case".

There are two methods you can use to change existing cell values to upper, lower, or proper case. The first method is to use a formula to change the case. The second method is to use a VBA procedure.

To change case with a formula, insert a blank column next to the column whose case you wish to change. In that column enter one of the following formulas, depending on what case you want as the result. Change the reference A1 to the first cell in your range.

Next, fill this formula down in the new column as far as you need to go to convert all your cells. Finally, copy the new column, select the first cell in the original range, A1 in this example, and choose Paste Special from the Edit menu. In that dialog, choose the Values item in the Paste options. You can now delete column you added. Note that this method should not be used if your original range has formulas in it. The paste operation will overwrite any existing formulas with static text values.

You can also change the case of a range of cells with a VBA procedure. Insert the following function into a standard code module (created with the Module item on the Insert menu in VBA). Uncomment (remove the apostrophe from) the line of code that changes the text to the case you want.

Sub ChangeCase()
    Dim Rng As Range
    On Error Resume Next
    Err.Clear
    Application.EnableEvents = False
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
             xlTextValues).Cells
        If Err.Number = 0 Then
           ' Rng.Value = StrConv(Rng.Text, vbUpperCase)
           ' Rng.Value = StrConv(Rng.Text, vbLowerCase)
           ' Rng.Value = StrConv(Rng.Text, vbProperCase)
        End If
    Next Rng
    Application.EnableEvents = True
End Sub

To change the case, select the cells you want to change and then run the macro (ALT F8). The SpecialCells property ensures that the code will not change cells with formulas.

Changing Case On Entry

You can use the Change event procedure to automatically change the case when text is entered into a cell.

DEFINITION: Event Procedure
An Event Procedure is a VBA procedure that is automatically called by Excel when a particular action occurs. For example, the Change event is automatically called when the value of a cell is changed by the user or by other VBA code (but not as the result of a calculation). Event procedures are described in detail on the Event Procedures page.

In the VBA editor, select the worksheet code module for the worksheet whose cells are to be converted, and paste the following code. Change the reference to A1:A10 to the range whose values are to be converted. Uncomment the line of code (remove the leading apostrophe) that converts the text to the desired format.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub

You can use Excel's Data Validation tool to prevent the user from entering anything except text in the desired case. Select the range of cells to which you want to apply Validation, choose Validation from the Data menu and choose Custom from the Allow list. In the formula box, enter any one of the following formulas, depending on what case you want to allow. Change the reference from A1 to the address of the first cell in the selected range.

=EXACT(A1,UPPER(A1))
=EXACT(A1,LOWER(A1))
=EXACT(A1,PROPER(A1))

With this validation in place, the user can enter only upper, lower, or proper case.