How to Change Text into Sentence Case in Excel (2 Easy Ways)

Converting text to sentence case is a common formatting task often required in Excel. A sentence case is where just the first letter of the first word in a text string is capitalized and the rest are in lowercase. Regretfully, unlike for just upper or lower case, Excel does not have an integrated function for sentence cases. However, you can still do it efficiently by using formulas or VBA codes.

Key Takeaways

Steps to use combination of several functions in a formula to change sentence case in Excel:

➤ Suppose your text is in cell A2. Create a separate column B adjacent to it.
➤ Enter the following formula in cell B2:
=UPPER(LEFT(A1,1)) & LOWER(MID(A1,2,LEN(A1)))
➤ Press Enter. You’ll see the sentence-case result in cell B2.
➤ Drag the Fill handle down to apply the formula to other rows.

In this article, we will walk you through all the possible methods you can use to change text to sentence case in Excel. There are two different methods- implementing a formula and VBA macro which are needed for different situations. We will let you know everything about these methods below.

Download Practice Workbook

Use a Formula to Change Sentence Case in Excel

Since Excel doesn’t have a direct SENTENCE function, you can use a combination of LOWER, UPPER, and LEFT functions in a formula. This formula converts the first letter to uppercase and the rest of the characters to lowercase. This method works the best when the original text is a single sentence with no proper nouns or abbreviations.

Suppose, this is the type of dataset we are dealing with where each cell consists of one sentence text strings:

Let’s take a look at how you can apply the formula to change these text strings to sentence case automatically in these Excel cells:

➤ Create a new column adjacent to the text strings to apply the formula in. name this column B as Sentence Case Text.

➤ Click on cell B2 and put in the formula:

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

Explanation

➥ Here, the LEFT(A2,1) function takes the first character of the text which we need to turn into capital letters.
➥ UPPER function converts the first character to uppercase and LOWER function converts the rest of the string to lowercase.
➥ MID(A1,2,LEN(A1)) takes the rest of the string starting from the second character to turn it into lowercase.

➤ Click and drag the fill handle beside cell B2 until cell B6 to apply the formula in all the cells of column B.

➤ Now, every cell in column B will start with a capital letter followed by small letters. So, column B has successfully transformed into sentence case texts.

Insert VBA Macro to Change Sentence Case in Excel

If you want to apply sentence cases across many cells or over multiple sentences within a single cell—this method is the best. Only a VBA code can change multiple, normal text strings into automatic sentence cases with a simple code.

Suppose, this is our dataset type where there are multiple sentences in one single cell:

You can’t apply the formula from the previous method in this case as that will only change the first letter to capital letter. Every other sentence in the cell will still start in small letters in that case. To use the VBA Macro method in this case, follow these steps below:

➤ Copy paste the entire column A into column B and name it as Sentence Case Text. This way, the original text won’t be altered by the code. You can skip this step if you want to change the sentence case in the original column itself.
➤ Select the column you want to apply the VBA code into.

➤ Open your Excel Workbook and press Alt + F11 to open the Visual Basic Editor window.

➤ Right click on the worksheet containing your data then go to Insert > Module.

➤ In the new module, copy and paste the following code.

Sub SentenceCaseMultipleSentences()
    Dim cell As Range
    Dim txt As String
    Dim i As Long
    Dim isNewSentence As Boolean
    For Each cell In Selection
        If Not IsEmpty(cell.Value) Then
            txt = LCase(cell.Value)
            txt = Application.WorksheetFunction.Trim(txt)
            isNewSentence = True
            For i = 1 To Len(txt)
                If isNewSentence And Mid(txt, i, 1) Like "[a-z]" Then
                    Mid(txt, i, 1) = UCase(Mid(txt, i, 1))
                    isNewSentence = False
                ElseIf Mid(txt, i, 1) Like "[.!?]" Then
                    isNewSentence = True
                End If
            Next i
            cell.Value = txt
        End If
    Next cell
End Sub

➤ The code will look like this in the module:

➤ Go to Run > Run Sub/UserForm to activate the code.

➤ From the File, select Close and Return to Microsoft Excel option to go back to our worksheet.

➤ Now, each sentence in every cell will transform into a sentence case automatically in our worksheet.

Frequently Asked Questions

Does Excel Have a Built-in Function for Changing to Sentence Cases?

No, Excel does not have a built-in function specifically for changing text strings to sentence cases. There are separate functions such as the UPPER(), LOWER(), and PROPER() functions. However, none of these capitalize only the first letter of a sentence unless they are combined into a formula.

Can I Apply Sentence Case to Multiple Sentences in One Cell Using a Formula?

A formula will not change the sentence case in Excel if one cell contains multiple sentences. Excel formulas cannot detect punctuation marks like periods to know where each sentence ends. Hence, only the first sentence will be properly changed and the rest will remain the same. You will need to use VBA macro in this case.

Can I Use the PROPER Function to Change Sentence Case in Excel?

The PROPER function capitalizes the first letter of every word. For example, Sandra Emailed The Client This Morning. But a sentence case capitalizes only the first letter of the first word in a sentence. For example, Sandra emailed the client this morning. So, the PROPER function can’t be used to achieve this result.

Wrapping Up

Despite Excel’s lack of an integrated sentence case feature, this guide has demonstrated that you have a number of efficient workarounds at your disposal. There is a formula based method which is perfect for simple cases and datasets where each cell contains only one sentence. On the other hand, VBA Macro Method is perfect for automation and bulk processing, especially when working with multiple rows or multi-sentence entries.

By choosing the correct method between these two, you won’t need to do any of the work manually anymore!

Post Views: 26