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.
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.
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:
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:
Finally concatenate these two results by using the CONCAT() function:
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