In this post, we will show you how to capitalize the first letter of a text string in Microsoft Excel. When exporting data to Excel, text strings are often not properly formatted. They may appear in title case, sentence case, uppercase, or a mix of random cases. If you’re dealing with such data and need to adjust the capitalization, this guide is for you.
The easiest way to capitalize text in Excel is by using the PROPER function. It capitalizes the first letter of each word in a text string while converting all other letters to lowercase. However, if you need to capitalize only the first letter and keep the rest unchanged, neither the PROPER function nor any other single Excel function can do that.
How to capitalize first letter of a text string in Excel
To capitalize the first letter of a text string in Excel, you can use a formula that combines multiple functions. However, you cannot apply a formula directly within the same cell that contains the original data. Instead, you need to use an extra column to apply the formula.
Once the formula is applied and the capitalization is corrected, you can copy and paste the formula cell values into the original column and then remove the extra column.
Let us understand this with the help of an example. Suppose we have a sample dataset as shown in the image below:
In the above data, we need to change the case of the first letter of each text string in Column B from lower to upper. We can achieve this capitalization in two different ways:
- Capitalize the first letter and convert the rest to lowercase
- Capitalize the first letter and keep the rest unchanged
Let us see this in detail.
1] Capitalize the first letter and convert the rest to lowercase
First of all, we will add an extra column to apply the formula. Take your cursor on top of Column B, right-click it and select Insert.
This will shift the content of Column B to Column C and insert an extra column in between.
Now place your cursor in cell B2 and write the following formula:
=REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))
Press Enter.
The above formula will capitalize the first letter of the text string which is in cell C2 and convert the rest into lowercase. Here’s how:
LOWER(C2)converts the entire text in cell C2 to lowercase.LEFT(C2,1)extracts the first letter from C2.UPPER(LEFT(C2,1))converts that first letter to uppercase.REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))takes the lowercase text from step 1 and replaces the first character (1st position, 1 character long) with the uppercase letter from step 3.
Now take your cursor to the bottom-right corner of cell B2 and when it converts to a plus sign, click and drag it to cell B11.
This will quickly copy the formula down the column.
2] Capitalize the first letter and keep the rest unchanged
Insert an extra column in between Columns A and B as explained above. Now place your cursor in B2 and type the following formula:
=REPLACE(C2,1,1,UPPER(LEFT(C2,1)))
Press the Enter key.
The above formula will capitalize only the first letter of the text string in cell C2 while keeping the rest of the text unchanged. Here’s how:
LEFT(C2,1)extracts the first character from the text in cell C2.UPPER(LEFT(C2,1))converts that character to uppercase.REPLACE(C2,1,1,UPPER(LEFT(C2,1)))replaces the first character (1st position, 1 character long) in C2 with the uppercase letter from step 2. The rest of the text remains unchanged.
Another formula that combines the LEFT, UPPER, MID, and LEN functions can achieve the same result. Place your cursor in cell B2 and write the following formula:
=UPPER(LEFT(C2)) & MID(C2,2,LEN(C2)-1)
Press Enter.
The formula capitalizes only the first letter of the text string while keeping the rest of the text unchanged. Here’s a breakdown:
LEFT(C2)extracts the first character from the text in C2 (Excel assumes the num_chars argument to be 1 by default when it’s not specified in the LEFT function).UPPER(LEFT(C2))converts the first letter to uppercase.MID(C2,2,LEN(C2)-1)extracts the remaining part of the text starting from the second character.LEN(C2)-1ensures that all characters except the first one are included.- Concatenation (&) combines the capitalized first letter
(UPPER(LEFT(C2)))with the remaining text(MID(C2,2,LEN(C2)-1)).
Copy the formula to the rest of the data cells in Column B through drag and drop. Once done, select cells B2:B11 and press Ctrl + C (copying the cell values). Now select cells C2:C11, right-click, and select Paste as Values. You may now delete the extra column, Column B.
That’s all. I hope you find this useful.
Read: How to make an Attendance Sheet in Excel.
How do you capitalize the first letter in a string in Excel?
To capitalize the first letter of every word in a string in Excel, you may use the PROPER function. To capitalize only the first letter leaving the remaining string unchanged, you may write custom formulas. Apart from that, you may use Power Query, VBA macros, or third-party add-ins to perform advanced text case formatting.
How do I auto capitalize the first letter in Excel?
To auto-capitalize the first letter in Excel, you can use the formula =UPPER(LEFT(A1,1)) & MID(A1,2,LEN(A1)-1). This converts the first letter to uppercase and keeps the rest of the text unchanged. You can enter this formula in a separate column, then copy and paste the results back over the original data if needed.
Read Next: Excel not opening on Windows computer.
