How to Format Text to Capitalize First Letter in Excel (10 Ways)

Sometimes there is data in the Excel worksheet where the letter case is wrong or not in the format that you want. In this article, we will show you how to format the text to capitalize the first letter in Excel in 10 different ways.

Download Workbook

You can download the free practice Excel workbook from here.

Overview

In the following image, we showed you the overview of the formulas based on the formatting of the text to capitalize the first letter that occurs.

10 Ways to Format Text to Capitalize the First Letter in Excel

Now, let’s dive into the deep. In this section, we will show you the details of every formula and VBA code on how to format the text to capitalize the first letter in Excel.

Look at the above image. It will be the dataset of this article. We will perform formulas and VBA code in the Output column (Column C) to capitalize the first letter of the texts in the Input column (Column B) based on different criteria.

1. Capitalize the First Letter of Every Word in a Cell in Excel

To capitalize the first letter of every word stored in a cell, you can execute the PROPER function in Excel.

All you have to do is, just pass the cell reference that you want to format inside the parentheses of the PROPER function.

In the following, we will show you how to capitalize the first letter of every word in Cell B5 for our dataset.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C5 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

Look at the above image for the result. Text in Cell B5 is now formatted in Cell C5 in a way that all the first letters of every word in that cell are now capitalized.

Read More: How to Capitalize Each Word in Excel (7 Ways)

2. The REPLACE Function to Capitalize Only the First Letter and Lower-Case the Rest

If you want to capitalize only the first letter of a string and lower the rest of the texts of a cell, then you can utilize Excel’s REPLACE function along with the LOWER and the UPPER function.

We will see the formula to capitalize only the first letter of the string and lower the rest of the string for Cell B6.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C6 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

As a result, the string in Cell B6 is now formatted in Cell C6 in a way that only the first letter is now capitalized and the rest of the string is in lowercase.

3. Capitalize Only the First Letter and Keep the Rest as It is in Excel

You can also utilize the REPLACE and the UPPER function to format texts in Excel.

We will see how to capitalize only the first letter of Cell B7 and keep the rest as they are with the functions mentioned.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C7 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

Finally, the string in Cell B7 is now formatted in Cell C7 in a way that only the first letter is now capitalized and the rest of the string is as it were before.

4. Format Text to Capitalize the First Letter with the UPPER, LEFT, RIGHT and LEN Functions

In the above, you have seen how to capitalize the first letter with Excel’s REPLACE, LOWER and UPPER functions. You can perform the same task with the UPPER, LEFT, RIGHT and LEN functions together in Excel.

We will learn how to capitalize only the first letter of Cell B8 with the mentioned functions together.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C8 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

As a result, the text in Cell B8 is formatted in Cell C8 in a way that only the first letter is now capitalized.

5. Capitalize First Letter with the UPPER, LEFT, LOWER, RIGHT and LEN Functions

You can use the LOWER function along with all the functions mentioned in the previous section to capitalize only the first letter of a cell and lower the rest of the texts.

We will see how to capitalize only the first letter of Cell B9 with the functions mentioned together.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C9 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

Consequently, the text in Cell B9 is now formatted in Cell C9 in a way that only the first letter is capitalized.

6. Capitalize Only the First Letter with the UPPER, LEFT, MID and LEN Functions

The MID function in Excel is also utilized in capitalizing the first letter.

Here, we will learn how to capitalize only the first letter of Cell B10 with the MID function along with other functions.

Steps:

• In the beginning, pick any cell to store the result. For our case, it is Cell C10 that holds the result.
• In that cell, write the formula shown below:

• Then, press Enter.

As we can see in the image above, the text in Cell B10 is now formatted in Cell C10 in a way that only the first letter is capitalized, and the rest are in lowercase.

7. Embed VBA to Capitalize Only the First Letter and Keep the Rest as It is in Excel

If you are an advanced user, then this section and the sections below are for you. Because you can implement VBA code to capitalize text. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel.

Here, we will see how to capitalize only the first letter of a string and keep the rest as they are with VBA code in Excel.

Steps:

• In the beginning, select the cell that you want to format to define it as the active cell (in our case, it is Cell C5).
• Then, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Following that, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub CapitalizeFirstLetterFirstWord()
Dim iRng As Range
Set iRng = Selection
For Each cell In iRng
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
Next cell
End Sub

Your code is now ready to run.

• Then, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the image below to see the result.

Finally, the text in the selected cell (Cell C5) is now formatted with VBA in a way that only the first letter is now capitalized, and the rest of the string is as it was before.

8. Apply VBA Macro to Capitalize Only the First Letter and Lower-Case the Rest in a Cell

This time we will see how to capitalize only the first letter of the string and lower the rest of the string for Cell C6 with VBA code.

Steps:

• First, select the cell that you want to format to define it as the active cell (in our case, it is Cell C6).
• Then, the same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Following that, copy the following code and paste it into the code window.

Sub CapitalizeFirstLetter()
Dim iRng As Range
Set iRng = Selection
For Each cell In iRng
cell.Value = Application.WorksheetFunction.Replace(LCase(cell.Value), 1, 1, UCase(Left(cell.Value, 1)))
Next cell
End Sub

Your code is now ready to run.

• Later, Run the macro and look at the image below for the result.

As a result, the string in Cell C6 is now formatted with VBA in a way that only the first letter is now capitalized, and the rest of the string is in lowercase.

9. Implement VBA Macro to Capitalize the First Letter of Every Word in a Range of Cell

To capitalize the first letter of every word in Cell C7 for our dataset with VBA code, follow the steps shown in this section.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

Sub CapitalizeFirstLetterAllWord()
Dim iRng As Range
Dim iValue As Range
On Error Resume Next
xTitleId = "Microsoft Excel"
Set iValue = Application.Selection
Set iValue = Application.InputBox("Select Range", xTitleId, iValue.Address, Type:=8)
For Each iRng In iValue
iRng.Value = Application.Proper(iRng.Value)
Next
End Sub

Your code is now ready to run.

• Then, Run this piece of code. A pop-up input box will appear. This time, select the cell (Cell C7) or range of cells that you want to format.
• Later, press OK.

After the successful code execution, look at the image below to find out what happened as a result.

Lastly, as we can see in the above picture, all the first letters of every word in Cell C7 are now capitalized.

10. VBA Macro to Capitalize the First Letter of Every Word of a Dataset in Excel

To capitalize the first letters of every word of a dataset in Excel, follow the steps below.

Steps:

• As shown previously, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

Sub CapitalizeFirstLetterEachWord()
Dim i As Long
    For i = 1 To 10
        Cells(i, 2) = StrConv(Cells(i, 2), vbProperCase)
    Next
End Sub

Your code is now ready to run.

• Lastly, Run this code. See the following image.

As a result, all the first letters of every word in all the cells of our dataset are now capitalized.

Conclusion

To conclude, this article showed you 10 different ways with Formula and VBA code on how to format the text to capitalize the first letter in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• Change Lowercase to Uppercase in Excel (6 Methods)
• How to Stop Auto Capitalization in Excel (with Quick Steps)
• Change Lowercase to Uppercase in Excel Without Formula
• How to Capitalize All Letters Without Formula in Excel (4 Quick Methods)