How to Capitalize the First Letter in Excel (3 Methods)

In Excel sometimes we need to change the case of the first letter from lower to upper. Do we have to change every character manually? Not anymore! Here in this article, I will share three methods to capitalize the first letter in Excel


Download Practice Workbook

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


3 Suitable Methods to Capitalize the First Letter in Excel

Consider the following dataset containing some book names. You might want to change only the first letter of the sentence, the first letter of each word of the sentence.

Capitalize the First Letter


1. Apply the PROPER Function to Capitalize the First Letter of Each Word

We can capitalize the first letter of each word very easily as excel has a dedicated function for this. By applying the PROPER Function, the first letter of each word is changed to uppercase. Let’s follow the steps below to learn!

Step 1:

  • Click on the first cell of the column where you want to get the result (C5).
  • Enter the PROPER The formula is,
=PROPER(B5)
  • Where, B5 is the required argument.

Apply the PROPER Function to Capitalize the First Letter

Step 2:

  • Press Enter to see the result. Now we can see that we have capitalized the first letter of each word.

Apply the PROPER Function to Capitalize the First Letter

  • After that, move your cursor to the bottom right corner of the cell until you get the fill handle icon. After getting it, drag down the fill handle to apply the same formula to the rest of the cells.

Apply the PROPER Function to Capitalize the First Letter

  • Now you will notice that all the first letter of each word have converted to uppercase form.

Sometimes, all the data may contain upper case. In that case, we may need to change them to lower case. With the help of PROPER Function we can solve it. Check this link.

Read More: How to Capitalize All Letters Without Formula in Excel (4 Quick Methods)


2. Use a Formula to Capitalize the First Letter of the First Word

In the previous method, we have capitalized all the first letters of a word. Now, we will demonstrate two different ways to make only the first letter capital and the rest of the letter will remain lower.

2.1 Combine the UPPER, LEFT, RIGHT, and LEN Functions

With the combination of UPPER, LEFT, RIGHT, and LEN functions, we will make only the very first letter capital in the cells. Let’s see!

Step 1:

  • Click on the first cell of the column where you want the converted result (C5).
  • Then we will apply the following formula-
=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)

Where,

  • LEFT(B5,1) = extracts the first character from the cell.
  • UPPER= Converts the first letter to Upper case.
  • RIGHT(B5,LEN(B5)-1)= Concatenates the string.

Combine the UPPER, LEFT, RIGHT, and LEN Functions

Step 2:

  • Then, by pressing Enter, we will get the desired result in the cell.

Combine the UPPER, LEFT, RIGHT, and LEN Functions

  • After that, dragging down the fill handle, we will receive output in all the cells.

Combine the UPPER, LEFT, RIGHT, and LEN Functions

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


2.2 Merge the REPLACE, LOWER, UPPER, and LEFT Functions

To convert only the first letter of the word we can use the REPLACE Function too. In this following method, we are gonna share that.

Step 1:

  • Click on the first cell of the column where you want the result (C5).

Then we will apply the following formula-

=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))

Where,

  • (LOWER(B5)= It converts all the texts into lower case.
  • UPPER(LEFT(B5,1)= This converts only the first letter into uppercase from the string.
  • The REPLACE Function is used to check the full string with only one change.

Merge the REPLACE, LOWER, UPPER, and LEFT Functions

Step 2:

  • After applying the formula, press Enter to get the output.

Merge the REPLACE, LOWER, UPPER, and LEFT Functions

  • With the help of the Flash Fill feature, we will get the result in all the cells.

Merge the REPLACE, LOWER, UPPER, and LEFT Functions

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


3. Run a VBA Code to Capitalize the First Letter in Excel

Using formulas takes some extra steps to get the result. By using the VBA code we just have to set it once. In the following method, Using VBA code we are going to capitalize the first letter of each word.

Step 1:

  • Press ALT+F11 keys to open Microsoft Visual Basic for Applications window.

Run a VBA Code to Capitalize the First Letter in Excel

  • Click Insert > Module, Paste the following code in the window.
Sub Capitalize()

Dim c As Range
Dim xyz As Range
On Error Resume Next
xTitleId = "Capitalize the First Letter"
Set xyz = Application.Selection
Set xyz = Application.InputBox("Range", xTitleId, xyz.Address, Type:=8)
For Each c In xyz
c.Value = Application.Proper(c.Value)
Next
End Sub

Run a VBA Code to Capitalize the First Letter in Excel

Step 2:

Then press F5 to run the code. Select the data range which you want to use ($B$5:$B$12).

Run a VBA Code to Capitalize the First Letter in Excel

  • Click OK to get the result. Now we can see that our first letters are capitalized!

Run a VBA Code to Capitalize the First Letter in Excel

Read More: How to Capitalize Each Word in Excel (7 Ways)


Things to Remember

▶ You can go to the Microsoft Visual Basic Editor window by pressing ALT + F11 or from the Developer Tab.

▶ To apply the same formula to the rest of the cells of your dataset, you can use the AutoFill tool.


Conclusion

Thank you for reading this article. Hope you have found all the possible ways to capitalize the first letter. If you have any queries regarding this, feel free to ask us. We, the Exceldemy Team, are always responsive to your queries.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo