Making the first letter in a cell uppercase is one of the most common text-cleaning tasks in Excel. Why? Text often loses its correct letter case during processing.
For example, you might need to add words or text to the beginning of multiple cells. If the original first letter was uppercase, you would first convert the entire cell to lowercase (for example, with the LOWER function), then add the new word, and finally make its first letter uppercase.
Or perhaps the text lost its punctuation and letter case after being normalized by a database script or by another person, and the original was deleted without a backup.
Your task is to make the text readable again by capitalizing the first letter in each cell.
If the dataset is small, you can do this manually, but when you have dozens, hundreds, or thousands of cells, it quickly becomes impossible without automation.
So how can we automate this task and make the first letter in a cell uppercase in Excel using formulas?
Short answer for those in a hurry: if the first character in your cells is always a letter, use =REPLACE(A1,1,1,UPPER(LEFT(A1,1))) from the first section and stop reading here. If the text may start with quotes, brackets or digits, read on: depending on the assumptions you make, there are several ways to solve this, and I’ll go from simple to more complex.
Make the first character in a cell uppercase
As the heading suggests, this formula is built on the following assumption:
The first character in the cell is always a letter. If not, we don’t care about the case of the letters that follow.
With that assumption, the task is simple: take the first character, convert it to uppercase with the UPPER function, and put it back into the string. You can get the first character from a cell in many ways; the easiest is the LEFT function:
=LEFT(A1,1)
The rest of the text can be extracted with the MID function. The complete formula looks like this:
=UPPER(LEFT(A1,1))&MID(A1,2,LEN(A1))
You can also do without extracting the remaining text by using the REPLACE function. This is my favorite option: it replaces exactly one character at position 1 with its uppercase version.
=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))
Note that both formulas leave the rest of the text unchanged. If the cell is written in all caps (“HELLO WORLD”), the result stays “HELLO WORLD”. To get “Hello world”, lowercase the rest first:
=UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1)))
Here is how these formulas behave on real data:
| Before | REPLACE + UPPER | UPPER + LOWER |
|---|---|---|
| john smith | John smith | John smith |
| NEW YORK office | NEW YORK office | New york office |
| “quoted” text | “quoted” text | “quoted” text |
| 2 apples | 2 apples | 2 apples |
The last two rows show the weak spot of this approach: if a cell starts with a quote or a digit, nothing changes. That’s what the next sections are about.
Make the first letter of the first word uppercase
Isn’t that the same as the option above? Partly, yes. But sometimes the first character in a cell is not a letter: it could be brackets, various kinds of quotes, an ellipsis, or other punctuation marks.
In such cases, the formulas above won’t work: they take that character, try to change its case (which does nothing), and put it back.
So we make a more complex assumption:
The first letter in the string is always in the first word.
Formally, punctuation marks are not part of a word. But when you need to get the first word from a cell, the first word is everything before the first space. The formula below searches for that space.
Here is the complete formula. For a detailed explanation of how it works, see the PROPER function.
=IFERROR(PROPER(LEFT(A1,SEARCH(" ",A1)-1))&REPLACE(A1,1,SEARCH(" ",A1)," "),PROPER(A1))
The beauty of PROPER is that it ignores punctuation (as well as digits and other non-letter characters) and really does capitalize the first letter of words. In this formula, we simply tell it to do so for the first word only. If the cell contains a single word with no spaces, IFERROR passes the whole cell to PROPER.
Keep one side effect in mind: PROPER capitalizes every letter that follows a non-letter character. So “(e-mail” becomes “(E-Mail”, and “it’s” in the first word becomes “It’S”. If such words are common in your data, use the universal formula below.
Find the position of the first letter in a cell
As mentioned above, these formulas are not perfect, because they rely on certain assumptions.
If you truly want to capitalize the very first letter in a cell, in other words, the first character that is a letter, you need to find its position first.
Position of the first English letter
If we assume again that only the English letters A–Z count as letters, an array formula with the SEARCH function will do the job.
It is very similar to the formulas used to find uppercase letters in a cell. The difference is that it uses MIN instead of COUNT: the task is not to count the matches and compare the result with zero, but to find the smallest position, which is the position of the first letter.
{=MIN(IFERROR(SEARCH(CHAR(ROW(65:90)),A1),""))}
Here ROW(65:90) returns the numbers 65 to 90, and the CHAR function turns them into the letters A to Z. SEARCH is not case-sensitive, so lowercase letters are found too.
Note that the formula contains IFERROR again: SEARCH returns an error for every letter that isn’t in the cell, and without IFERROR, MIN would return an error too. If the cell has no letters at all, the formula returns 0.
This works well for English text, but letters with diacritics (é, ü, ñ) and letters of other alphabets are not in the A–Z range. For text like that, you need the next formula.
Position of the first letter in any language
Is there a formula that finds the first letter in a string, in any language? Yes, there is! It relies on a property that only letters have: they can change case, unlike digits, punctuation marks, and other characters.
When a letter changes case, it actually becomes a different Unicode character. That is easy to check with the EXACT function or with the UNICODE function.
The first formula uses EXACT for the comparison. It compares the lowercase and uppercase versions of each character and returns the position of the first one where they differ:
{=MATCH(FALSE,EXACT(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),0)}
The second one uses UNICODE:
{=MIN(IF(UNICODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<>UNICODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1))),""))}
These are array formulas. In Excel 2019 and earlier, type them without the curly braces and press Ctrl+Shift+Enter: the braces will appear in the formula automatically. In Excel 365 and Excel 2021, just press Enter.
In Excel 365, the same check is much shorter thanks to the SEQUENCE function and LET:
=LET(c,MID(A1,SEQUENCE(LEN(A1)),1),MATCH(FALSE,EXACT(LOWER(c),UPPER(c)),0))
These formulas may look complex and bulky, and they are, but they seem to be the best Excel can offer for finding the position of the first letter without compromises.
Capitalize any first letter
Now we have everything we need for the formula that does what we wanted from the start: it truly capitalizes the first letter in cell A1.
It is based on the same pair, REPLACE and MID. REPLACE replaces text at a given position, MID extracts the character at the same position, and UPPER makes it uppercase:
=REPLACE(A1,POSITION,1,UPPER(MID(A1,POSITION,1)))
Instead of the word POSITION, we insert the formula from the previous section that finds that position.
Here is the final formula:
{=REPLACE(A1,MATCH(FALSE,EXACT(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),0),1,UPPER(MID(A1,MATCH(FALSE,EXACT(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)))}
In Excel 365, LET lets you calculate the position once and reuse it. IFERROR returns the cell unchanged if it is empty or contains no letters:
=IFERROR(LET(c,MID(A1,SEQUENCE(LEN(A1)),1),p,MATCH(FALSE,EXACT(LOWER(c),UPPER(c)),0),REPLACE(A1,p,1,UPPER(MID(A1,p,1)))),A1)
This formula finds the first character that has an uppercase form and capitalizes only that character, leaving the rest of the cell as is. Here is how it compares with the simple version:
| Before | =REPLACE(A1,1,1,…) | Final formula |
|---|---|---|
| “quoted” text | “quoted” text | “Quoted” text |
| (see note) | (see note) | (See note) |
| 2 apples | 2 apples | 2 Apples |
| …éclair | …éclair | …Éclair |
Why capitalization fails: common mistakes
- The formula returns #NAME?. LET and SEQUENCE exist only in Excel 365 and Excel 2021. In older versions, use the array formulas with ROW(INDIRECT(…)).
- Only the first character changed, the rest is still in caps. REPLACE and UPPER don’t touch the rest of the text. Wrap the rest in LOWER, as shown in the first section.
- The array formula returns a wrong position or #VALUE!. In Excel 2019 and earlier, it was entered with Enter instead of Ctrl+Shift+Enter. Check that curly braces appeared around the formula.
- The result is correct, but the source cells didn’t change. A formula always returns the result in another cell. To keep only the result, replace the formulas with values and delete the source column.
Capitalize sentences in one click
The formula above solves the task. But if a cell contains several sentences and each of them must start with a capital letter, there is no formula solution at all.
Instead of keeping a text file with long formulas like the one above, you can use an Excel add-in that solves the task in one click. My !SEMTools add-in is one of them.
It saves you from bulky array formulas and complex function combinations, as well as from creating helper columns and deleting them once you get the result.
In other words, it saves time and nerves when working with text. It contains over 500 ready-to-use tools, one of which capitalizes the first letters of sentences. Select the range, click the button on the !SEMTools tab, and that’s it:
See also:
- Change case of text in Excel
- Find uppercase letters in Excel
- Add text to multiple cells at once
- Bulk replace values in Excel
Capitalize the first letter of every sentence right in the source cells, without formulas or helper columns.
!SEMTools solves this and hundreds of other text tasks in a couple of clicks!
Summary: which way to capitalize the first letter
I hope you found what you were looking for on this page and were able to capitalize the first letter in your cells. If your text always starts with a letter, the simple REPLACE + UPPER formula will do. If it may start with quotes, brackets or digits, you need the final formula, or its short LET version in Excel 365.
But any of these formulas still has to be remembered, typed in, filled down the column and then replaced with values, and for several sentences in one cell there is no formula at all. With !SEMTools, you select the range and click the button: the first letters become uppercase right in place, without helper columns.
You may also be interested in related operations: find uppercase letters in cells, remove extra spaces or extract letters from text.