Sentence Case in Excel

August 06, 2018 - by Suat M. Ozgur

Neethu asked today's question in a comment on YouTube:

Can a macro change text to Sentence Case in Excel?

It is strange: Excel knows UPPER, lower and Proper, but it does not support the other cases supported by Word: Sentence Case or tOGGLE cASE.

Selected text case can be easily changed in Microsoft Word by using the internal function called Change Case.

Change Case command options in Microsoft Word.

You can simply click:

"Sentence Case" to capitalize the first letter of a sentence and leave all other letters as lowercase.
"lowercase" to exclude capital letters from your text.
"UPPERCASE" to capitalize all of the letters.
"Capitalize Each Word" to capitalize the first letter of each word and leave the other letters lowercase.
"tOGGLE cASE" to shift between two case views.

Although Excel is not a word processing application, sometimes you might need to change the case of the given text. There are three Excel functions to provide similar functionality. These functions take single argument, and transform the case of the provided text or referenced cell's text value as explained below.

LOWER() function to exclude capital letters.
UPPER() function to capitalize all of the letters.
PROPER() function to capitalize the first letter of each word.

While we wouldn't discuss Tooggle Case option in this article, Sentence Case option might be necessary to use in Excel, and this can be partially achieved by combining the existing functions for a single sentence as shown below.

Select Case implementation with Excel formulas.

You can use following function combinations to apply Select Case to a given sentence in Excel.

Take the first letter of the given text by using the LEFT() function, and transform it to uppercase by using the UPPER() function:
=UPPER(LEFT(A1,1))
And take the rest of the text by combining the RIGHT() and LEN() functions together, and transform it to lower case by using the LOWER() function:
=LOWER(RIGHT(A1,LEN(A1)-1))
Finally concatenate these two results by using the CONCAT() function:
=CONCAT(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN(A1)-1)))

This will transform the text to sentence case. You can also test this for all capitalized text which is shown in A2 cell.

What if there are more than a single sentence in a cell that you'd like to change to Sentence Case?

One option to do this could be using VBA to make this transformation.

SENTENCECASE() user defined function takes the given text, process the text for three punctuation marks (period, question mark, and exclamation point) to find the multiple sentences, capitalize the first letter of each sentence, and return the result.

Function SENTENCECASE(txt As String)
Dim resArr() As String
Dim newArr1(), newArr2(), newArr3() As Variant
    ReDim resArr(0)
    resArr(0) = txt
    newArr1 = splitAndTransform(resArr(0), ".")
    If Not IsEmpty(newArr1) Then
        For Each par1 In newArr1
            newArr2 = splitAndTransform(par1, "?")
            If Not IsEmpty(newArr2) Then
                For Each par2 In newArr2
                    newArr3 = splitAndTransform(par2, "!")
                    If Not IsEmpty(newArr3) Then
                        For Each par3 In newArr3
                            resArr(UBound(resArr)) = par3
                            ReDim Preserve resArr(UBound(resArr) + 1)
                        Next par3
                    End If
                Next par2
            End If
        Next par1
    End If

    SENTENCECASE = Join(resArr, " ")
End Function

SENTENCECASE() function uses a helper function called splitAndTransform() to split sentences, and transform the case by the given delimiter. splitAndTransform() is a reusable VBA function in this project, so it is written as a separate helper function.

Function splitAndTransform(text, delimiter)
Dim tmpArr
Dim newArr
    tmpArr = Split(text, delimiter)
    If UBound(tmpArr) >= 0 Then
        ReDim newArr(UBound(tmpArr))
        For i = 0 To UBound(tmpArr)
            If tmpArr(i) <> "" Then
                newArr(i) = Trim(tmpArr(i))
                newArr(i) = UCase(Left(newArr(i), 1)) & _
                            LCase(Right(newArr(i), Len(newArr(i)) - 1))
                If Not isPuncMarked(newArr(i)) Then
                    newArr(i) = newArr(i) & delimiter
                End If
            End If
        Next i
    Else
        ReDim newArr(0)
    End If
    splitAndTransform = newArr
End Function

splitAndTransform() helper function uses another helper function called isPuncMarked() that defines if the given text contains a punctuation mark at the end. Even it is not reused in the module, isPuncMarked() function returns a boolean value, and the caller function is only concerned the value it returns, but how it works. It is always a good practice to also separate this logic to provide better readability in dependent procedures.

Function isPuncMarked(sentence) As Boolean
Dim rightMost As String
    rightMost = Right(sentence, 1)
    If rightMost = "." Or _
        rightMost = "?" Or _
            rightMost = "!" Then
            isPuncMarked = True
    Else
        isPuncMarked = False
    End If
End Function

Here is the result.

Ideally it might be good idea to write a procedure that will take the selected range, and replace all the content by using Sentence Case instead a user defined function. This can be done by adding the following sub procedure into the project that will apply the mass and permanent transforming.

Sub doSentenceCase()
Dim rng As Range
Dim cll As Range
Dim resArr() As String
Dim newArr1(), newArr2(), newArr3() As Variant

    Set rng = Selection
    For Each cll In rng.Cells
        ReDim resArr(0)
        resArr(0) = cll.Value
        newArr1 = splitAndTransform(cll.Value, ".")
        If Not IsEmpty(newArr1) Then
            For Each par1 In newArr1
                newArr2 = splitAndTransform(par1, "?")
                If Not IsEmpty(newArr2) Then
                    For Each par2 In newArr2
                        newArr3 = splitAndTransform(par2, "!")
                        If Not IsEmpty(newArr3) Then
                            For Each par3 In newArr3
                                resArr(UBound(resArr)) = par3
                                ReDim Preserve resArr(UBound(resArr) + 1)
                            Next par3
                        End If
                    Next par2
                End If
            Next par1
        End If
        cll.Value = Join(resArr, " ")
    Next cll
End Sub

Title Photo: Aaron Burden / Unsplash