How to Use a Formula to Convert Text to Sentence Case in Excel?

In Excel, you can easily convert the text string into Sentence Case and it will helps you to read and engageable. While Excel doesn’t have a built-in “sentence case” function, you can achieve this formatting using a custom formula. In this tutorial, I will explain you the multiple methods to convert a text string into a sentence case format in Excel using Formula and VBA codes.

If you want to convert a text string into a valid Capitalized Case and then you can use the Excel PROPER function. The Excel PROPER Function simply converts the text strings into a 1^st letter capitalized strings (Each 1^st letter of a word will be capitalized in a sentence).

Method 1: Using Formula to Convert Text into Sentence Case:

The below formula converts any text, whether it’s in all capital letters or all lowercase, into sentence case.

=UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)-1))

Formula Explanation:

• LEFT(B4,1) – This part of the formula extracts the first character of the text in cell B4.
• UPPER(LEFT(B4,1)) – This section converts that first character to uppercase.
• MID(B4,2,LEN(B4)-1) – This formula, extract the second part of the text string.
• LOWER(MID(B4,2,LEN(B4)-1)) – Which converts the text string into lowercase.
• & – Simply appends the text strings.

Functions Used	Function Explanation
LEFT	Extracts a specified number of characters from the start (left side) of a text string.
UPPER	Converts all letters in a text string to uppercase.
MID	Returns a specific number of characters from a text string, starting at a specified position.
LEN	Returns the total number of characters in a text string.
LOWER	Converts all letters in a text string to lowercase.

That’s it. This is how you can use the formula to convert any text strings into a sentence case text strings.

Method 2: Using VBA Code to Convert Text into Sentence Case Strings

This is the alternative method to do. You might need to use the VBA code to convert any text string into sentence case strings.

• First, you need to use the keyboard shortcut ALT + F11 to launch the VBA window.
• Then from the menu bar, you need to choose Insert -> Module.

• Now, you need to copy & paste the below vba code to convert all text strings into sentence case strings.

Sub SentenceCase()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Sentence Case by Excel24x7.com"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xValue = Rng.Value
    xStart = True
    For i = 1 To VBA.Len(xValue)
        ch = Mid(xValue, i, 1)
        Select Case ch
            Case "."
            xStart = True
            Case "?"
            xStart = True
            Case "a" To "z"
            If xStart Then
                ch = UCase(ch)
                xStart = False
            End If
            Case "A" To "Z"
            If xStart Then
                xStart = False
            Else
                ch = LCase(ch)
            End If
        End Select
        Mid(xValue, i, 1) = ch
    Next
    Rng.Value = xValue
Next
End Sub

• Before executing the code, you need choose the cell range that you want to convert into a sentence case strings.

• Now, You need to use the Keyboard shortcut F5 to execute the command.
• The mini popup window will appear on you screen and in that the selected range value pre-entered by default.

• Once you check the selected range values, click the OK button to convert all the cell values into a Sentence case text strings.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

Recently Published Tutorials:

• How Can You Reverse Signs of Cell Values in Excel?
• How to Sync Selected Range Across All Worksheets in Excel?
• How Can You Convert an Angle Between Degrees and Radians in Excel?
• How to Merge & Combine Columns without Losing Data in Excel?
• Excel File Options Shortcuts List & It’s Explanations!