To capitalize the first letter of every word, use =PROPER(A2) — “john smith” becomes “John Smith”. To capitalize only the first letter of the cell and lowercase everything else, use =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1)).
And to capitalize the first letter while leaving the rest of the text exactly as it is, drop the LOWER: =UPPER(LEFT(A2,1))&RIGHT(A2,LEN(A2)-1) — that’s the one to use when the text contains acronyms or proper nouns you don’t want flattened. Two things to watch.
PROPER capitalizes after any non-letter, so “don’t” becomes “Don’T” and “o’brien” becomes “O’Brien” — usually right for the second, wrong for the first. And an empty cell returns #VALUE! with the two manual formulas, because LEN(A2)-1 goes negative; wrap them in =IFERROR(...,"") if blanks are possible.
For a one-off fix with no formula, type the corrected version next to your first entry and press Ctrl+E for Flash Fill.
Let’s say you have a list of text values, and you want to capitalize the first letter for each text. This thing can be easily done using a simple formula. And in this tutorial, we will learn to write this formula to capitalize the first letter of a word.
PROPER Function to Capitalize First Letter
In the following example, you have a list of names, and all these names are in lower letters. Now you need to convert the first letter into capital.
Use the following steps to capitalize the first letter.
- Type equal in cell B2.
- Enter the TEXT function and type the starting parentheses.
- Refer to cell A2 where you have text for which you capitalize the first letter.
- In the end, enter closing parentheses and hit enter to the result.
Only Capitalize the First Letter of the First Word
Now let’s take another situation where you only need to capitalize only the first letter of the first word and not the rest of the words. You can use the following formula for that.
=UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
To understand this formula, we need to break it into two parts.
The first part of the formula uses the UPPER and LEFT function that extracts the first letter from the text and converts it into the capital letter.
And the second part of the formula uses RIGHT, LOWER, and LEFT functions. These functions take the rest of the part of the text and convert that part into a lower letter text.
But if you want to capitalize the first letter and want to leave the rest of the part as it was, you can amend the above formula. See the following.
=UPPER(LEFT(A2,1))&RIGHT(A2,LEN(A2)-1)This formula will not make any changes to the text other than the first letter of the first word.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.