Method 1 – Combining UPPER, RIGHT, LEFT, and LEN Functions to Make the First Letter of a Sentence Capitalized in Excel
Steps:
- Go to Cell C5 and put the following formula:
=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)
- Press the Enter button and pull down the Fill Handle icon.
All the first letters of each sentence are now capitalized.
Formula Explanation:
- LEN(B5)
It finds out the length of Cell B5.
Result: 33
- RIGHT(B5,LEN(B5)-1)
We get data from the right side of Cell B5.
Result: “ uality is the best business plan “
- LEFT(B5,1)
We get data from the left side of Cell B5.
Result: q
- UPPER(LEFT(B5,1))
Capitalize on the previous result.
Result: Q
Read More: How to Change Sentence Case in Excel
Method 2 – Joining REPLACE, LEFT, UPPER, and LOWER Functions for Making the First Letter of a Sentence Capitalized
Steps:
- Words are in random cases in the sentence.
- Put the following formula in Cell C5.
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))
- Hit Enter and drag the Fill Handle icon.
Sentences are in the proper format now.
Formula Explanation:
- LEFT(B5,1)
Gets the 1st letter of Cell B5.
Result: q
- UPPER(LEFT(B5,1))
Capitalizes on the previous result.
Result: Q
- LOWER(B5)
Lowercases the data of Cell B5.
Result: “quality is the best business plan”
- REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))
Replaces the 1st letter data with the upper case letter get from the previous section.
Result: “Quality is the best business plan”
Read More: How to Change Case in Excel Sheet
Method 3 – Making the First Letter of a Sentence Capitalized by Merging UPPER, LEFT, MID, and LEN Functions
Steps:
- Put the formula below in cell C5.
=UPPER(LEFT(B5))&MID(B5,2,LEN(B5))
- Hit the Enter button and pull the Fill Handle icon to get the result.
Formula Explanation:
- LEN(B5)
Finds out the length of Cell B5.
Result: 33
- MID(B5,2,LEN(B5))
Gets data from the middle with the 2nd position from Cell B5.
Result: “ uality is the best business plan”
- LEFT(B5)
Gets the leftmost letter of Cell B5.
Result: q
- UPPER(LEFT(B5))
Capitalizes on the previous result.
Result: Q - UPPER(LEFT(B5))&MID(B5,2,LEN(B5))
Combines two previously performed formulas.
Result: “ Quality is the best business plan”
Read More: How to Change Case for Entire Column in Excel
Method 4 – Using Excel VBA to Make the First Letter of a Sentence Capitalized
Steps:
- Go to the Sheet Name section at the bottom of the datasheet.
- Right-click on the sheet name.
- Click on the View Code option from the Context Menu.
- The VBA window appears. Choose the Module option from the Insert tab.
- This is the VBA module window.
- Insert this VBA code in the module.
Sub Capitalize_Letter_First()
Dim text_1 As Range
Set text_1 = Selection
For Each cell_1 In text_1
cell_1.Value = Application.WorksheetFunction.Replace _
(LCase(cell_1.Value), 1, 1, UCase(Left(cell_1.Value, 1)))
Next cell_1
End Sub- Save the code.
- Select the Range C5:C9.
- Press F5 to run the code.
Download the Practice Workbook
Related Articles
- How to Change Lowercase to Uppercase with Formula in Excel
- Excel VBA to Capitalize First Letter of Each Word
- How to Change Lowercase to Uppercase in Excel Without Formula
- Change Upper Case to Lower Case in Excel
- How to Change Lowercase to Uppercase in Excel
- How to Change Case in Excel without a Formula
- How to Capitalize First Letter of Each Word in Excel
<< Go Back to Change Case | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
