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 1st letter capitalized strings (Each 1st letter of a word will be capitalized in a sentence). PROPER function PROPER Function 1st letter capitalized strings 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)) =UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)-1)) ![Formula Converted the input text strings into a Sentence Case Strings ](https://cdn.hackernoon.com/images/KRhE4Gz4QZeX1uc46YQTGSYAHND2-t013907.jpeg) 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. LEFT(B4,1) – This part of the formula extracts the first character of the text in cell B4. LEFT(B4,1) first character UPPER(LEFT(B4,1)) – This section converts that first character to uppercase. UPPER(LEFT(B4,1)) MID(B4,2,LEN(B4)-1) – This formula, extract the second part of the text string. MID(B4,2,LEN(B4)-1) LOWER(MID(B4,2,LEN(B4)-1)) – Which converts the text string into lowercase. LOWER(MID(B4,2,LEN(B4)-1)) & – 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. 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. Functions Used

Function Explanation Functions Used Functions Used Function Explanation Function Explanation LEFT

Extracts a specified number of characters from the start (left side) of a text string. LEFT LEFT LEFT Extracts a specified number of characters from the start (left side) of a text string. 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. UPPER UPPER UPPER Converts all letters in a text string to uppercase. 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. MID MID MID Returns a specific number of characters from a text string, starting at a specified position. 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. LEN LEN LEN Returns the total number of characters in a text string. Returns the total number of characters in a text string. LOWER

Converts all letters in a text string to lowercase. LOWER LOWER LOWER Converts all letters in a text string to lowercase. 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. VBA code 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. 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. Insert Module Choose Module from the Insert Menu Now, you need to copy & paste the below vba code to convert all text strings into sentence case strings. 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 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. 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. 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. This tutorial is originally published on How to Use a Formula to Convert Text to Sentence Case in Excel? 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. This tutorial is originally published on How to Use a Formula to Convert Text to Sentence Case in Excel? Once you check the selected range values, click the OK button to convert all the cell values into a Sentence case text strings. OK That’s it. This tutorial is originally published on How to Use a Formula to Convert Text to Sentence Case in Excel? How to Use a Formula to Convert Text to Sentence Case in Excel

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Range

How to Instantly Delete All Objects and AutoShapes in Excel

Reverse Excel Cell Signs With the Paste Special & VBA in Easy Steps

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Step-by-Step: Transform Text Case in Excel Using Formula

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps