How to Capitalize First Letter of Sentence in Excel

When you import data from other external systems or user entries, some texts become all lowercase and inconsistently capitalized. Capitalizing the first letter is essential to give your dataset a clean professional formatting. Excel’s UPPER function is useful for this task.

To visually demonstrate the methods, we used a customer feedback dataset. Column D (Recommend) has our targeted sentences with lower casings for the first letters. We’ll capitalize the first letters and keep the rest as they are.

Excel’s flash fill feature automatically recognizes patterns and applies them to the chosen cells. If you have a smaller dataset, use the flash fill feature like this:

➤ Choose a neighboring cell to the text string that you want to convert.
➤ Type the correct version of the sentence with the first letter capitalized. You can also create a combination of uppercase and lowercase letters following a general pattern.

➤ Now, select all the cells of the column and go to the Data tab. From the Data Tools group, click on the Flash Fill icon. Or, use the keyboard shortcut  CTRL  +  E .

➤ Excel will fill the remaining cells with capitalized first letters. In case it misses any cells or it doesn’t work at all, select another cell and repeat the process.

With the UPPER function, you can capitalize any text string you want. However, we’re working with full sentences, so we need to use it with other functions like LEFT, MID, and LEN to apply uppercase to the first letter only. Let’s get to the process:

➤ Create a helper column to put the corrected sentences. Select a cell adjacent to the first text string you want to correct.
➤ Type the following formula to apply uppercase only to the first letter of the sentence and keep the original casing of the rest of the sentence:

➤ If you want to force lowercase on the remaining letters, type this formula instead:

➤ For each formula, replace D2 with the cell reference containing the sentence with incorrect casing.
➤ Press Enter and use the fill handle to drag the formula down to the remaining cells.

Another way to easily capitalize the first letter of a sentence is to use the REPLACE function. It replaces the letter and changes it based on the given arguments.

So, we used it with the UPPER and LEFT function that applies uppercase to the first letter. Adding a LOWER function forces lower casing to the remaining letters. Here’s how to apply the formula:

➤ Select a cell adjacent to the target cell with the incorrect sentence. Insert the following formula:

➤ If you don’t want to force lowercase on the remaining letters, type this formula instead:

➤ Instead of D2, use the cell reference of your target cell.
➤ Press Enter and drag the formula down to the remaining cells.

To capitalize the first letter of each word of the sentence without a formula, you can use the Power Query tool. It’s a dynamic approach to the problem that automatically updates the future inputs.

However, with Power Query, there’s no way to capitalize only the first letter of the first text of a sentence. Follow these steps to use the tool and capitalize all the first letters:

➤ Select your data range and go to the Data tab. Choose Get & Transform from the Get & Transform Data group and click on the From Table/Range icon.

➤ When Excel prompts you to prompt you to turn your dataset into a table, you can either click Ok to accept it or press Cancel.

➤ If you cancel table creation, go to the Name Box and create a one-word name for your dataset. Press Enter to open the Power Query tool.

➤ In the Power Query editor, select the column you want to capitalize. Go to the Transform tab, choose Format, and select Capitalize Each Word from the drop-down menu.

➤ Go back to the Home tab. Click Close & Load to apply the change and load it back into Excel.

➤ If you don’t like the custom Power Query format, go to Table Design and clear the formatting, or choose one from the Quick Styles option.

If you want to capitalize the first letter of one or multiple sentences, VBA coding is your only option. Follow the steps given below to create your own custom formula with VBA:

➤ To open the Developer tab, go to File >> More >> Options.

➤ Click on Customize Ribbon, choose Developer, and click Ok.

➤ As the Developer tab appears on the main ribbon, select the helper column cells, click on Developer and choose Visual Basic to open the VBA editor.

➤ Click on Insert, select Module.

➤ In the Module Box, paste the following code:

Function CapitalizeSentences(ByVal txt As String) As String
    Dim result As String
    Dim i As Integer
    Dim isNewSentence As Boolean
    txt = LCase(txt)
    isNewSentence = True
    result = ""
    For i = 1 To Len(txt)
        Dim ch As String
        ch = Mid(txt, i, 1)
        If isNewSentence And ch Like "[a-z]" Then
            result = result & UCase(ch)
            isNewSentence = False
        Else
            result = result & ch
        End If
        If ch = "." Or ch = "!" Or ch = "?" Then
            isNewSentence = True
        ElseIf ch <> " " And ch <> vbTab And ch <> vbCrLf Then
            isNewSentence = False
        End If
    Next i
    CapitalizeSentences = result
End Function
Sub CapitalizeLetters()
End Sub

➤ Now, press  F5  or click on Run from the top ribbon. Choose Run Sub/UserForm from the drop-down menu.
➤ Go back to the Excel tab and type this formula in a helper column:

➤ Press Enter and drag it down to other cells. The formula capitalizes the first letter of each sentence (after ., !, or ?) while the rest stays in lowercase.

➤ To save the file in correct format, click on the Files tab and choose Save As.
➤ As you locate the desired location to save the file, click on the arrow sign beside the Save as Type box. Choose Excel Macro-Enabled Workbook and click Save.

Frequently Asked Questions

Can you change the case of letters in Excel?

Yes, you can use the PROPER, UPPER, or LOWER function to change letter cases in Excel. To capitalize all the first letters of a text string, type the following formula in its adjacent cell:

=PROPER(D2)

Make all the letters uppercase with this formula below:

=UPPER(D2)

Type the formula below to convert all the letters to lowercase:

=LOWER(D2)

Replace D2 with the cell reference of your text string.

How do you check if the first letter is capital in Excel?

To check if the first letter of your text string is capitalized, select its neighboring cell and enter the following formula:

=EXACT(LEFT(D2,1), UPPER(LEFT(D2,1)))

Put the appropriate cell reference instead of D2. The formula returns TRUE if the first letter is capitalized and FALSE otherwise.

How do I capitalize all text in Excel without formula?

To capitalize all text in Excel without using a formula, create a helper column and type the capitalized version of the text in the first row. For example, type JANE AUSTIN if the original is jane austin. Now, select all the cells and press  Ctrl  +  E  to trigger Flash Fill and apply the same capitalized text pattern to all the selected cells.

Concluding Words

Without a doubt, using the Flash Fill feature is the easiest way to capitalize the first letter of a sentence. However, it might not be as effective for large and complex datasets.

Applying a formula or VBA coding is more reliable, but you need to create a separate column to apply these methods. However, you can delete the column anytime after copying its data into the original column.