Macro to Change Text Case (UPPER, lower, Proper)

These VBA macros change the text in the selected cells to UPPER CASE, lower case, Proper Case or Sentence case, in place, without helper columns. Numbers, dates and formulas are left untouched.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

UPPER CASE

Select the cells, then run:

Sub MakeUpperCase()
    Dim c As Range
    For Each c In Selection
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            c.Value = UCase(c.Value)
        End If
    Next c
End Sub

VarType(c.Value) = vbString makes sure only text changes. Without it, numbers would be turned into text.

lower case and Proper Case

Same macro, with a different function on the middle line:

Sub MakeLowerCase()
    Dim c As Range
    For Each c In Selection
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            c.Value = LCase(c.Value)
        End If
    Next c
End Sub

Sub MakeProperCase()
    Dim c As Range
    For Each c In Selection
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            c.Value = StrConv(c.Value, vbProperCase)
        End If
    Next c
End Sub

Sentence case

Capital first letter, everything else lowercase:

Sub MakeSentenceCase()
    Dim c As Range
    For Each c In Selection
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            c.Value = UCase(Left(c.Value, 1)) & LCase(Mid(c.Value, 2))
        End If
    Next c
End Sub

Without a macro

Formulas need a helper column and a Paste Values step afterwards. The macros change the cells directly. See UPPER, LOWER and PROPER, or use the free text case converter for pasted text.

Things to know

• Proper Case capitalises every word, including words like and and of.
• Cells with formulas are skipped, so formulas aren’t replaced with text.
• Add the macros to your Personal Macro Workbook to use them in every file.

Related macros