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

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.


Download Practice Workbook

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


4 Methods to Make First Letter of Sentence Capital in Excel

Now, we will discuss 4 methods to capitalize the first letter of a sentence. We will work on the following dataset.

1. Combine UPPER, RIGHT, LEFT and LEN Functions to Make First Letter of a Sentence Capital

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 (6 Easy Methods)


2. Combine REPLACE, LEFT, UPPER and LOWER Functions

In this section, we use the combination of REPLACE, LOWER, UPEER, 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 (8 Quick Methods)


3. Merge 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 (7 Amazing Ways)


4. Use VBA to Make First Letter of 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.


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 have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo