Sabrina Ayon
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.
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.
- 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.
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.
- And, that’s all. You can see all the first letters of each word are now capitalized in column C.
Read More: How to Capitalize First Letter of Sentence in Excel (6 Suitable Methods)
Similar Readings
- How to Format Cell and Center Text with Excel VBA (5 Ways)
- Change Lowercase to Uppercase in Excel Without Formula
- How to Change Case in Excel without a Formula (5 Ways)
- Excel VBA: Change Font Color for Part of Text (3 Methods)
- [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
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.
- 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.
- 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.
- 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.
- 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.
- 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
- How to Change Font Size of the Whole Sheet with Excel VBA
- Change Lowercase to Uppercase in Excel (6 Methods)
- How to Stop Auto Capitalization in Excel (with Quick Steps)
- Capitalize All Letters Without Formula in Excel (4 Quick Methods)
- How to Add Text after Number with Custom Format in Excel (4 Ways)
