Musiha Mahfuza Mukta
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.
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.
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.
- 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.
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.
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.
- 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.
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.
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.
- 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
- How to Capitalize All Letters Without Formula in Excel (4 Quick Methods)
- Change Lowercase to Uppercase in Excel Without Formula
- How to Change Lowercase to Uppercase in Excel (6 Methods)
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to Use Select Case Statement in Excel VBA (2 Examples)
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.
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.
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.
- 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
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.
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.
- Now, choose the Options Menu.
- Now, from Proofing >> go to AutoCorrect Options.
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.
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.
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.
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.
