Text Case Transformations in Excel VBA

Overview

Excel VBA Text

Excel has UPPER, LOWER, and PROPER functions, but they require helper columns and do not modify cells in place. These six VBA macros transform selected cell text directly, including Title Case with proper article handling, Toggle Case, and Sentence Case.

Each macro calls a shared TransformTextInSelection engine that reads the selection into an array, applies the transformation, and writes back. Formula cells are preserved untouched.

The Six Modes

UPPERCASE: Converts all characters to uppercase using VBA's UCase function
lowercase: Converts all characters to lowercase using LCase
Proper Case: Capitalizes the first letter of every word using WorksheetFunction.Proper
Title Case: Like Proper Case but keeps articles, prepositions, and conjunctions lowercase (e.g., 'of', 'the', 'in')
tOGGLE cASE: Inverts each character's case, useful for fixing accidental Caps Lock text
Sentence case: Capitalizes only the first letter after each period, producing natural sentence formatting

The VBA Code

Sub Uppercase(): TransformTextInSelection "upper": End Sub
Sub Lowercase(): TransformTextInSelection "lower": End Sub
Sub Propercase(): TransformTextInSelection "proper": End Sub
Sub Titlecase(): TransformTextInSelection "title": End Sub
Sub ToggleCase(): TransformTextInSelection "toggle": End Sub
Sub SentenceCase(): TransformTextInSelection "sentence": End Sub

Private Sub TransformTextInSelection(TextMode As String)
    Dim Row_Idx As Long, Col_Idx As Long
    Dim Cel As Range, Slc_Rng As Range

    For Each Slc_Rng In Selection.Areas
        Dim Arr() As Variant
        ReDim Arr(1 To Slc_Rng.Rows.Count, 1 To Slc_Rng.Columns.Count)
    
        For Row_Idx = 1 To Slc_Rng.Rows.Count
            For Col_Idx = 1 To Slc_Rng.Columns.Count
                Set Cel = Slc_Rng.Cells(Row_Idx, Col_Idx)
                If Not Cel.HasFormula Then
                    Select Case LCase(TextMode)
                        Case "upper":    Arr(Row_Idx, Col_Idx) = UCase(CStr(Cel.Value))
                        Case "lower":    Arr(Row_Idx, Col_Idx) = LCase(CStr(Cel.Value))
                        Case "proper":   Arr(Row_Idx, Col_Idx) = WorksheetFunction.Proper(CStr(Cel.Value))
                        Case "title":    Arr(Row_Idx, Col_Idx) = Title_case(Cel.Value)
                        Case "toggle":   Arr(Row_Idx, Col_Idx) = ToggleCaseText(CStr(Cel.Value))
                        Case "sentence": Arr(Row_Idx, Col_Idx) = SentenceCaseText(CStr(Cel.Value))
                    End Select
                Else
                    Arr(Row_Idx, Col_Idx) = Cel.Formula
                End If
            Next Col_Idx
        Next Row_Idx
        Slc_Rng.Value = Arr
    Next Slc_Rng
End Sub

Private Function Title_case(sInp As String) As String
    Dim asInp() As String, i As Long
    If Len(sInp) Then
        asInp = Split(LCase(sInp))
        asInp(0) = StrConv(asInp(0), vbProperCase)
        For i = 1 To UBound(asInp)
            Select Case asInp(i)
            Case "about", "above", "across", "after", "against", "along", _
                 "among", "around", "as", "at", "before", "behind", "below", _
                 "beneath", "beside", "between", "beyond", "but", "by", _
                 "down", "during", "except", "for", "from", "in", "inside", _
                 "into", "of", "off", "on", "onto", "out", "outside", "over", _
                 "past", "through", "to", "toward", "under", "up", "upon", _
                 "with", "without"
                ' Skip - keep lowercase
            Case Else
                asInp(i) = StrConv(asInp(i), vbProperCase)
            End Select
        Next i
        Title_case = Join(asInp)
    End If
End Function

Private Function ToggleCaseText(ByVal Txt As String) As String
    Dim i As Long, ch As String, result As String
    For i = 1 To Len(Txt)
        ch = Mid(Txt, i, 1)
        If ch = UCase(ch) Then result = result & LCase(ch) _
        Else result = result & UCase(ch)
    Next i
    ToggleCaseText = result
End Function

Private Function SentenceCaseText(ByVal Txt As String) As String
    Dim i As Long, ch As String, result As String, capNext As Boolean
    capNext = True
    For i = 1 To Len(Txt)
        ch = Mid(Txt, i, 1)
        If capNext And ch Like "[a-zA-Z]" Then
            result = result & UCase(ch): capNext = False
        Else
            result = result & LCase(ch)
        End If
        If ch = "." Then capNext = True
    Next i
    SentenceCaseText = result
End Function

Final Thoughts

The array-based approach makes these transformations fast even on large selections. The Title Case implementation with its list of lowercase exceptions follows standard English title capitalization rules, making it especially useful for formatting report headings and labels.