How to Capitalize the First Letter of a Sentence in Excel

Capitalizing the first word of a sentence in Excel is really easy and you can do that using a formula. In this article, you’ll learn the quickest way to do it.

In Microsoft Word, there is an excellent feature called Change Case. It will allow you to change any text to sentence case, lower case, upper case, capitalize each word and also toggle case, which will toggle uppercase and lowercase characters for you. Microsoft Excel doesn’t provide us with any built-in features like this. The reason is simple. Whereas MS Word is designed for writing and requires a change case feature, Excel is designed for calculation, and changing cases appear to be less important in Excel. But, in reality, it is just as important in Excel as it is in Word. There are some functions that allow us to change text to uppercase, lowercase and proper case. But to create a sentence case or to change the first letter capital in Excel requires a formula.

We have created a formula using the following functions:

Upper: The upper function basically changes any text into uppercase letters. Check out this video to see it in action.

Lower: The lower function changes any text into lowercase. Here is how you use it. How to Change Uppercase Letter to Lowercase in Excel.

Left: The Left function extracts the character from the left. Check out this video on how to use the left function in excel.

Right: The Right Function extracts characters from the right. Here is how you can use the right function in excel. 

Len: The Len function returns the length of a text string. Here is How to Use LEN Function in Excel.

Now here is the scenario. If you have sentences in column A and you need to convert each sentence in a way that only the first letter will be the capital. Then you should use the below formula to do that.

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

The formula works like this. By using the left formula, we extract the first character of the sentence. Then wrap it up with the upper function by making it uppercase or capital letter. Now we need to find how many characters are left in that sentence. We found that using the LEN function and then converting all of them into lowercase letters. Here we use the right function so that we only convert characters into lowercase from the right, leaving the first letter capital. We added the parts using the concatenate function, which is "&" in this case.

Now if you write this formula to cell B1, it will convert the data from cell A1. This is how you can capitalize the first letter in Excel using a formula.

Now that you are using a formula, there are some drawbacks. I’ve listed the drawbacks and their solutions below. 

1. You need to convert the formula result into a value. I’ve explained 6 ways to convert formula results into value in another article. You can use any one of the six to convert your formula results into value. You can use a different approach to make the first letter capital in Excel using VBA.

2. Sometimes we accidently remove or edit our formula that results in error. To solve this, you can learn to Lock Formula in Excel.

3. If you are using a formula, everyone can see the formula if they click on the cell containing the formula. You can solve this. You can easily hide formulas in excel.

Once you create the formula and overcome the possible problems that come with it, it is really easy to convert the first letter capital of a sentence in excel. Thanks a million for reading this. If possible, please support us by becoming a patron.