How to Capitalize First Letter of Each Word in Excel (4 Ways)

While working with Microsoft Excel, we may wish to capitalize the initial letter of each word when entering certain information into an Excel sheet, such as business names or employee names. In this article, we will look at the ways to capitalize the first letter of each word in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


4 Ways to Capitalize First Letter of Each Word in Excel

Users of Excel may need to change the case of text in their spreadsheets on occasion. And it could be done easily, just use the keyboard to manually alter the contents of the cells. But still while working with lots of data, we may mistakenly insert data incorrectly. We can solve the problem in many ways.

To capitalize the first letter of each word we are going to use the following dataset which contains some employee names in column B but in the wrong way. Now, we will correct the name in column C.

4 Ways to Capitalize First Letter of Each Word in Excel


1. Use Flash Fill Option to Capitalize First Letter of Each Word

Flash Fill allows us to enter data more quickly and accurately. Based on the initial item, it anticipates the rest of the data. To use the Flash Fill to capitalize the first letter of each word, let’s follow the quick steps below.

STEPS:

  • Firstly, select the cells and type the text with capitalized initial characters in a cell adjacent to the cell holding the content So, we select cell C5, and type the corrected name. In our example, tom smith as Tom Smith.
  • Secondly, to confirm the entry press Ctrl + Enter.

Use Flash Fill Option to Capitalize First Letter of Each Word

  • Finally, to use the Flash Fill option, press Ctrl + E.
  • And, that’s it. You will be able to see your desired result. This will automatically capitalize all the first letters for each word.

Use Flash Fill Option to Capitalize First Letter of Each Word

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


2. Capitalize First Letter of Each Word Using PROPER Function

The PROPER function transforms the initial character to the upper case and the other characters to the lowercase. The function in Excel converts user input text to the proper case. It is possible to use it to capitalize each word in a string. Let’s demonstrate the procedure to use it to capitalize the first letter of each word.

STEPS:

  • First, select the cell where you want to insert the formula to correct the names. So, we select cell  C5.
  • Second, put the formula into that cell.
=PROPER(B5)
  • Third, press Enter.

  • Further, to copy the formula over the range, drag the Fill Handle down or Double-click on the Plus (+) icon.

Capitalize First Letter of Each Word Using PROPER Function

  • And, that’s all. You can see all the first letters of each word are now capitalized in column C.

Capitalize First Letter of Each Word Excel

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


Similar Readings


3. Excel VBA Macros to Capitalize First Letter

VBA Macros employ the Visual Basic Application to build bespoke user-generated routines and simplify manual activities. We can use VBA Macros to capitalize the first letter of each word. So, let’s follow the steps below to use the VBA MAcros to capitalize each word’s first letter.

STEPS:

  • In the beginning, go to the Developer tab from the ribbon.
  • Then, to open the Visual Basic Editor, click on Visual Basic under the Code category.
  • Or, instead of doing this, just press Alt + F11 to open the Visual Basic Editor.

  • Another way to display the Visual Basic Editor is to right-click on your worksheet and click on View Code.

Excel VBA Macros to Capitalize First Letter

  • This will take you to the Visual Basic Editor, where you will write your codes.
  • After that, click on Module from the Insert drop-down menu.

Excel VBA Macros to Capitalize First Letter

  • Now, copy and paste the VBA code there.

VBA Code:

Sub CapitalizeFirstWord()
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select Range", "CapitalizeFirstWord", myRange.Address, Type:=8)
For Each myCell In myRange
myCell.Value = Application.Proper(myCell.Value)
Next
End Sub
  • Further, to save the code in your workbook, click on that save icon or press Ctrl + S. While saving the file, make sure you saved it as Macro enable means the .xlsm file.

Excel VBA Macros to Capitalize First Letter

  • Furthermore, back to the worksheet, and by the same token as before, go to the Developer tab on the ribbon.
  • Next, to run the macros click on Macros under the Code group.

Excel VBA Macros to Capitalize First Letter

  • This will appear in the Macro window.
  • Now, click on the Run button.

  • Select the range of cells that you want to capitalize the first letter of each word. So we select the range $B$5:$B$10.
  • And, then click OK.

  • And, you can finally see the result.

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


4. Apply Power Query to Capitalize First Letter

A Powerful Query helps in saving time that would have been spent directly in the previous. It enables each information refreshing to instantly update current or updated information. We can use Power Query to capitalize the first letter of each word. Let’s follow the steps down.

STEPS:

  • Firstly, go to the Data tab from the ribbon.
  • Secondly, select From Table/Range under Get & Transform Data category.

  • This will display the Create Table dialog box.
  • Now, select the range $B$4:$B$10 under Where the data for your table?
  • And, further, tick mark (‘’) the check box which is immediately on the left side of My table has headers.
  • Then, click OK.

Apply Power Query to Capitalize First Letter

  • This will take you to the Power Query window.
  • Further, select the table and right-click.
  • And, then, go to Transform.
  • From the drop-down menu, click on Capitalize Each Word.

  • This will capitalize the first letter of each word. Now, save it.

  • This will take you back to another worksheet named Table.
  • And, you can see the first word for each name is now capitalized.


Conclusion

The above methods will assist you to capitalize the first letter of each word in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo