How to Capitalize First Letter of Sentence in Excel (6 Suitable Methods)

Excel is not only for numerical values but also you may need to work with text data. So, if you know how to capitalize the first letter of a sentence in Excel, then you can input your data more easily. In this article, I have described how to capitalize the first letter of a sentence in Excel.


Download Practice Workbook

You can download the practice workbook from here:


6 Methods to Capitalize First Letter of Sentence in Excel

Here, I’m going to explain 6 methods of how to capitalize the first letter of a sentence in Excel. For your better understanding, I will use a sample dataset. Which has 5 sentences. The sample dataset is given below.

Excel Capitalize First Letter of Sentence


1. Using PROPER Function to Capitalize Each Word

We can use the PROPER function to capitalize each word of a sentence. The steps are given below.

Steps:

  • Firstly, you have to select a cell, where you want to keep the changes. I have selected the C5 cell.
  • Secondly, use the corresponding formula in the C5 cell.
=PROPER(B5)

Here, I have used the PROPER function. This function capitalizes all the first letters of every word.

  • Now, press ENTER.

At this time, you will see the result.

Excel Capitalize First Letter of Sentence

  • Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells C5:C9.

Finally, you will see the following result.

Excel Capitalize First Letter of Sentence

Read More: How to Capitalize First Letter of Each Word in Excel (4 Ways)


2. Applying UPPER, LEFT, LEN, and RIGHT Functions to Capitalize First Letter of Sentence

You can apply UPPER, LEFT, RIGHT, and LEN functions to capitalize only the first letter of a sentence keeping the rest the same.

Steps:

  • Firstly, select a different cell C5 where you want to see the result.
  • Secondly, use the corresponding formula in the C5 cell.
=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)

Formula Breakdown

  • LEN(B5)—-> The LEN function will give the total count of the characters in cell B5.
    • Output: 30
  • RIGHT(B5,30-1)—-> Now, The Right function will take the rightmost 30-1=29 characters of cell B5.
    • Output: ow to capitalize first letter
  • LEFT(B5,1)—-> Then, the LEFT function will take the leftmost character from cell B5.
  • UPPER(h)—-> Finally, the UPPER function will capitalize the letter “h”.
    • Output: H
  • “H”&“ow to capitalize first letter”—-> Finally it will concatenate the two texts and capitalize the first letter of the sentence in cell B5.
    • Output: How to capitalize first letter
  • Now, press ENTER to get the capitalized form.

Finally, you can see the capitalized 1st letter of the sentence.

Excel Capitalize First Letter of Sentence

  • Now, you have to drag the  Fill Handle icon to AutoFill the corresponding data in the rest of the cells C5:C9.

Finally, you will get all the formatted sentences.

Excel Capitalize First Letter of Sentence

Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)


3. Use of Combined Functions to Capitalize Only First Letter of Sentence

You can use combined functions to capitalize only the first letter of sentences. Here, I have used REPLACE, LOWER, UPPER, and LEFT functions. This formula makes the rest of the sentence in the lower form.

Steps:

  • Firstly, select a different cell C5 where you want to see the formatted sentences.
  • Secondly, use the corresponding formula in the C5 cell.
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))    

Formula Breakdown

  • LEFT(B5,1)—-> Here, the LEFT function will count the leftmost character from cell B5.
    • Output: h
  • UPPER(LEFT(B5,1))—-> Now, The UPPER function will capitalize “h”.
    • Output: H
  • LOWER(B5)—-> Then, the LOWER function will return every character from cell B5 in lower case.
  • REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))—-> Finally, the REPLACE function will replace the 1st letter “h” into “H
    • Output: How to capitalize first letter
  • Now, press ENTER to get the capitalized form.

Excel Capitalize First Letter of Sentence

  • At this time, you have to drag the  Fill Handle icon to AutoFill the corresponding data in the rest of the cells C5:C9.

Finally, you will get all the formatted sentences.


Similar Readings


4. Employing UPPER, LEFT, MID & LEN Functions

You can employ UPPER, LEFT, MID and LEN functions to capitalize only the 1st letter of a sentence. This formula keeps the rest as same.

Steps:

  • Firstly, select a different cell C5 where you want to see the formatted sentences.
  • Secondly, use the corresponding formula in the C5 cell.
=UPPER(LEFT(B5))&MID(B5,2,LEN(B5))   

Excel Capitalize First Letter of Sentence

Formula Breakdown

  • LEN(B5)—-> The LEN function will give the total count of the characters in cell B5.
    • Output: 30
  • MID(B5,2,30)—-> The MID function will extract 30 characters starting from the 2nd character in cell B5.
    • Output: ow to capitalize first letter
  • LEFT(B5)—-> Here, the LEFT function will count the leftmost character from cell B5.
    • Output: h
  • UPPER(“h”)—-> Now, The UPPER function will capitalize “h”.
    • Output: H
  • “H”&“ow to capitalize first letter”—-> Finally it will concatenate the two texts and capitalize the first letter of the sentence in cell B5.
    • Output: How to capitalize first letter
  • Now, press ENTER to get the capitalized form.

  • At this time, you have to drag the  Fill Handle icon to AutoFill the corresponding data in the rest of the cells C5:C9.

Finally, you will get all the formatted sentences.

Excel Capitalize First Letter of Sentence


5. Employing VBA to Capitalize First Letter of Sentence

You can employ VBA code to capitalize only the first letter of a sentence. The steps are given below.

Steps:

  • Firstly, you have to choose the Developer tab >> then select Visual Basic.

Excel Capitalize First Letter of Sentence

  • Now, from the Insert tab >> select Module.

  • Write down the following Code in the Module.
Sub Capitalize()
Dim Select_Range As Range
Set Select_Range = Selection
For Each Cell_Value In Select_Range
    Cell_Value.Value = UCase(Left(Cell_Value.Value, 1)) & _
    Right(Cell_Value.Value, Len(Cell_Value.Value) - 1)
Next Cell_Value
End Sub

Excel Capitalize First Letter of Sentence

Code Breakdown

  • Here, we have created a Sub Procedure named Capitalize.
  • Next, declared a variable Select_Range as Range to call the range.
  • Then, the Selection property will select the range from the sheet.
  • After that, we used a For Each Loop to calculate each selected cell using some VBA functions which are Ucase,Left,Right and Len functions.
  • By using these functions we have created a formula that will capitalize the first letter of a sentence.
  • Firstly, Save the code then go back to Excel File.
  • Secondly, select the range B5:B9.
  • Thirdly, from the Developer tab >> select Macros.

  • Then, select Macro (Capitalize) and click on Run.

Excel Capitalize First Letter of Sentence

Finally, you will see the result. In this case, only the 1st letter of the sentence will be capitalized.


6. Using Excel AutoCorrect Option to Capitalize Some Limited Words

You can use the Excel autoCorrect option to capitalize some limited words only. Absolutely, this is not an efficient method. Because you can capitalize only some defined words by this method. In addition, you can include more words as you want. As you have to do this manually, it can’t be an easy solution.

Steps:

  • Firstly, go to the File tab.

Excel Capitalize First Letter of Sentence

  • Now, choose the Options Menu.

  • Now, from Proofing >> go to AutoCorrect Options.

Excel Capitalize First Letter of Sentence

At this time, you will see the following AutoCorrect box.

  • Now, from the Replace box you have to write your target word and in the With box you have to write the word with the 1st letter as capital.
  • Then, select Add.
  • Similarly, you have to include other words.
  • Finally, click on OK.

xcel Capitalize First Letter of Sentence

At this time, write your defined word and press space or enter, then you will see the result.

Here, I have included How, Life, Love, Live, and Make as my defined word. Then, I got the following result.

xcel Capitalize First Letter of Sentence

Read More: How to Stop Auto Capitalization in Excel (with Quick Steps)


Things to Remember

  • In case you want to capitalize the 1st letter of the sentence and want to keep the rest as before, then method 2 or 4 will be the best option.
  • In case, you want to capitalize only the 1st letter of the sentence and keep the rest in the lower form, then you can use method 3.
  • In method 1, all the 1st letters of each word will be capitalized.

Practice Section

Now, you can practice by yourself.

xcel Capitalize First Letter of Sentence


Conclusion

I hope you found this article helpful. Here, I have explained 6 different ways to Capitalize the First Letter of a Sentence in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo