Wasim Akram
In Excel sometimes we need to change the case of the first letter from lower to upper. Do we have to change every character manually? Not anymore! Here in this article, I will share three methods to capitalize the first letter in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Methods to Capitalize the First Letter in Excel
Consider the following dataset containing some book names. You might want to change only the first letter of the sentence, the first letter of each word of the sentence.
1. Apply the PROPER Function to Capitalize the First Letter of Each Word
We can capitalize the first letter of each word very easily as excel has a dedicated function for this. By applying the PROPER Function, the first letter of each word is changed to uppercase. Let’s follow the steps below to learn!
Step 1:
- Click on the first cell of the column where you want to get the result (C5).
- Enter the PROPER The formula is,
=PROPER(B5)- Where, B5 is the required argument.
Step 2:
- Press Enter to see the result. Now we can see that we have capitalized the first letter of each word.
- After that, move your cursor to the bottom right corner of the cell until you get the fill handle icon. After getting it, drag down the fill handle to apply the same formula to the rest of the cells.
- Now you will notice that all the first letter of each word have converted to uppercase form.
Sometimes, all the data may contain upper case. In that case, we may need to change them to lower case. With the help of PROPER Function we can solve it. Check this link.
Read More: How to Capitalize All Letters Without Formula in Excel (4 Quick Methods)
2. Use a Formula to Capitalize the First Letter of the First Word
In the previous method, we have capitalized all the first letters of a word. Now, we will demonstrate two different ways to make only the first letter capital and the rest of the letter will remain lower.
2.1 Combine the UPPER, LEFT, RIGHT, and LEN Functions
With the combination of UPPER, LEFT, RIGHT, and LEN functions, we will make only the very first letter capital in the cells. Let’s see!
Step 1:
- Click on the first cell of the column where you want the converted result (C5).
- Then we will apply the following formula-
=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)Where,
- LEFT(B5,1) = extracts the first character from the cell.
- UPPER= Converts the first letter to Upper case.
- RIGHT(B5,LEN(B5)-1)= Concatenates the string.
Step 2:
- Then, by pressing Enter, we will get the desired result in the cell.
- After that, dragging down the fill handle, we will receive output in all the cells.
Read More: How to Capitalize First Letter of Sentence in Excel (6 Suitable Methods)
2.2 Merge the REPLACE, LOWER, UPPER, and LEFT Functions
To convert only the first letter of the word we can use the REPLACE Function too. In this following method, we are gonna share that.
Step 1:
- Click on the first cell of the column where you want the result (C5).
Then we will apply the following formula-
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))Where,
- (LOWER(B5)= It converts all the texts into lower case.
- UPPER(LEFT(B5,1)= This converts only the first letter into uppercase from the string.
- The REPLACE Function is used to check the full string with only one change.
Step 2:
- After applying the formula, press Enter to get the output.
- With the help of the Flash Fill feature, we will get the result in all the cells.
Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)
3. Run a VBA Code to Capitalize the First Letter in Excel
Using formulas takes some extra steps to get the result. By using the VBA code we just have to set it once. In the following method, Using VBA code we are going to capitalize the first letter of each word.
Step 1:
- Press ALT+F11 keys to open Microsoft Visual Basic for Applications window.
- Click Insert > Module, Paste the following code in the window.
Sub Capitalize()
Dim c As Range
Dim xyz As Range
On Error Resume Next
xTitleId = "Capitalize the First Letter"
Set xyz = Application.Selection
Set xyz = Application.InputBox("Range", xTitleId, xyz.Address, Type:=8)
For Each c In xyz
c.Value = Application.Proper(c.Value)
Next
End Sub
Step 2:
Then press F5 to run the code. Select the data range which you want to use ($B$5:$B$12).
- Click OK to get the result. Now we can see that our first letters are capitalized!
Read More: How to Capitalize Each Word in Excel (7 Ways)
Things to Remember
â–¶ You can go to the Microsoft Visual Basic Editor window by pressing ALT + F11 or from the Developer Tab.
â–¶ To apply the same formula to the rest of the cells of your dataset, you can use the AutoFill tool.
Conclusion
Thank you for reading this article. Hope you have found all the possible ways to capitalize the first letter. If you have any queries regarding this, feel free to ask us. We, the Exceldemy Team, are always responsive to your queries.
