Table of Contents
In this lesson, We’ll teach you how to Capitalize first letter in Excel in a few different ways. People use Excel for more than just numbers. They also use it for text data. It could be as basic as writing down names or more complicated. When working with text data, one common duty is to make the data consistent by capitalizing the initial letter in each cell.
Even though Excel is mostly used for spreadsheets with numbers, you will often need to put text in cells. Any table in a spreadsheet must contain column or row headers. Because of this, Excel users will sometimes need to change the way text is written in their spreadsheets. Of course, you could just use the keyboard to change the content of the cells by hand.
If you have a list of text strings containing words that are all capital, all lowercase, or a mix of both, you need to make the initial letter of each word in each cell uppercase and the rest lowercase. You can also go to the official Microsoft Excel support site to know more information.
Ways to Capitalize first letter in Excel
Capitalize First Letter of Each Word Using PROPER Function
The PROPER function changes the first letter to all capital letters and the rest to all lowercase letters. The function in Excel changes the case of text typed by the user. It can be used to change the case of every word in a string. Let’s show you how to utilize it to make sure that the first letter of each word is capitalized.
- 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.
- 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.
Apply Power Query to Capitalize First Letter
A strong query can save you time that you would have spent immediately in the past. It lets each time information is refreshed, new or updated information is included right away. We can utilize Power Query to make sure that every word’s first letter is capitalized. Let’s go through the 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.
Excel VBA Macros to Capitalize First Letter
VBA Macros leverage the Visual Basic Application to develop user-made routines and make things easier to do by hand. We can utilize VBA Macros to make sure that every word’s first letter is capitalized. So, let’s use the VBA macros to make sure that the initial letter of every word is capitalized.
- 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.
- 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.
Use Flash Fill Option to Capitalize First Letter of Each Word
Flash Fill lets us enter information faster and more correctly. It guesses the rest of the information based on the first piece of information. Follow these short instructions to utilize Flash Fill to capitalize the initial letter of each word.
- 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.