How to Make First Letter of Sentence Capital in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel has some built-in function that helps capitalize the first letter of a sentence. This article will discuss how to make the first letter of sentence capital in Excel.


1. Combining UPPER, RIGHT, LEFT and LEN Functions to Make the First Letter of a Sentence Capital in Excel

We will use a combination of the UPPER, RIGHT, LEFT, and LEN functions to uppercase the first letter of the sentence.

📌 Steps:

  • Go to Cell C5 and put the following formula based on the mentioned functions.
=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)

Combine UPPER, RIGHT, LEFT, and LEN Functions to Capitalize First Letter in a Sentence

  • Now, press the Enter button and pull 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


2. Joining REPLACE, LEFT, UPPER and LOWER Functions for Making First Letter of a Sentence Capital

In this section, we use the combination of REPLACE, LOWER, UPPER, and LEFT functions to upper case the first letter of a sentence.

📌 Steps:

  • We can see in the dataset that words are in random cases in the sentence.

We can see that words are not either in lower case or upper case. They are mixed, which is not normal in a sentence. We will capitalize the 1st letter of the sentence and lowercase the rest.

  • Put the following modified formula based on Cell C5.
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))

Combine REPLACE, LEFT, UPPER, and LOWER Functions to Capitalize the First Letter of a Sentence and Change the Rest to Lower Case

  • 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


3. Making First Letter of a Sentence Capital by Merging UPPER, LEFT, MID and LEN Functions

We will combine UPPER, LEFT, MID, and LEN functions here.

📌 Steps:

  • Put the formula below on Cell C5.
=UPPER(LEFT(B5))&MID(B5,2,LEN(B5))

Merge UPPER, LEFT, MID, and LEN Functions to capitalize letter in Excel

  • 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


4. Using Excel VBA to Make the First Letter of a Sentence Capital

We can also use a VBA code based on Replace, LCase, UCase, and Left functions to capitalize the first letter of the sentence.

📌 Steps:

  • First, go to the Sheet Name section at the bottom of the datasheet.
  • Press the right button of the mouse.
  • Click on the View Code option from the Context Menu.

Use VBA to Make First Letter of Sentence Capital

  • VBA window appears. Choose the Module option from the Insert tab.

Use VBA to Make First Letter of Sentence Capital

  • This is the VBA module window. We will write VBA code here.

  • Now, paste the VBA code on 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
  • Select Range C5:C9.

Use VBA to Make First Letter of Sentence Capital

  • Now, press F5 to run the code.

The first letter of each sentence is capitalized now.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we described 4 methods to make the first letter of sentence capital in Excel. We showed formulas and VBA macro for this. I hope this will satisfy your needs. Please give your suggestions in the comment box.


Related Articles


<< Go Back to Change Case | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo