Soumik Dutta
When we work on an Excel worksheet, sometimes we have to capitalize each word in a text. If you are facing any difficulties in doing that job, then you have come to the right place. In this article, we will explain 7 easy methods to capitalize each word in a text.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
7 Easy Ways to Capitalize Each Word in Excel
In our workbook, we have a name list of 10 persons in small text. The data which we will modify in the range of cells B4:B14 and the result will be in the range of cells C4:C14. We will demonstrate 7 different approaches to capitalize the first letter of each word.
Method 1: Manually Modify in Formula Bar to Capitalize First letters of Each Word
This is the easiest way to capitalize the first letter of each word in an Excel worksheet. Our data is in the range of cells B5:B14 and we will show the result in the range of cells C5:C14. For getting the result go through the following steps.
📌 Steps:
- First of all, select Cell B5 and press Ctrl+C. You can also click the right button on your mouse and select the Copy option.
- Now, press Ctrl+V to paste the text into Cell C5. You can also right-click on your mouse Cell C5 and select the Paste option.
- After that, go to the Formula Bar of your Excel worksheet and click after the letter ‘m’. Press the Backspace key on your keyboard and press Shift+m to get the block letter.
- Further, left-click after the letter ‘p’ and follow the same process to make the letter ‘P’. Press the Enter key to store the text in Cell C5.
- Similarly, go through the rest of the rows and follow the same process for all of them. The final result will be like the image given below.
👉 If you have a limited amount of data like us you can use this method. However, if you have a vast amount of data to modify, we recommend you to follow the other approaches.
Read More: How to Capitalize the First Letter in Excel (3 Methods)
Method 2: Use PROPER Function to Capitalize Each Word in Excel
In this method, we will use the PROPER function to capitalize each word in the Excel worksheet. The data we have to modify is in the range of cells B5:B14 and the result will be in the range of cells C5:C14. The steps are given below.
📌Steps:
- First, Select Cell C5. In this cell, write the following formula:
=PROPER(B5)- Press Enter on your keyboard to get the result.
- Now, double click the Fill Handle sign allocated on the right-bottom side of the cell to copy the formula up to Cell C15. Besides it, you can also simply drag the Fill Handle square box up to Cell C15.
- Finally, we will get each word in the block form in our desire cells.
Method 3: Apply Power Query Option to Capitalize Each Word
For getting the capitalized word in this method we have to go through the following procedure-
📌Steps:
- First, select the range of cells B4:B14.
- Next, go to the Data tab and select the From Table option from the Get & Transform.
- Afterward, a small dialog box will appear entitled Create Table. Click on My table has headers and finally click the OK.
- Next, the Power Query Editor will appear. In this box, go to Add Column tab and select the Format.
- Then, select the option Capitalize Each Word.
- After that, a new column will be added and you will get the first letter of each word in capital.
- Then, click on the Close Button sign in the right corner of that dialog.
- Â Consequently, Another small box will appear and click on Keep.
- After that, you will find a new sheet that will open in the Sheet names.
- Click on ‘Table1’ and you will get the result.
- Finally, we modified the format of the sheet and this is how it looks.
Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)
Method 4: Employ VBA to Capitalize Each Word of a Line in Excel
In this method, we will write a VBA code to capitalize each word of a text. The approaches are described as follows:
📌Steps:
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer Tab. Or You can also press Alt+F11 for opening the Visual Basic Editor.
- Afterward, a new window will appear.
- From the Insert tab, click Module.
- Then, write down the following visual code in that Module.
Sub capitalize_each_word()
Dim sheet_name As Worksheet
Dim working_range As Range, cell As Range
Dim last_row As Long
Set sheet_name = ThisWorkbook.Sheets("VBA")
With sheet_name
last_row = .Cells(.Rows.Count, "B").End(xlUp).Row
For Each cell In .Range("B5:B15" & last_row)
cell.Offset(0, 1).Value = Application.Proper(cell.Value)
Next
End With
End Sub- Close the Editor Now from the View ribbon, click on Macros > View Macros.
- After that, a new dialog box will appear. Select capitalize_each_word.
- Then, click on the Run button to run this code and the result will appear.
- In the end, we can say that our visual code worked perfectly and we got the result at our desired cells.
Method 5: Use Microsoft Word to Capitalize Each Word
In this process, we need to take help from another software of Microsoft, called ‘Microsoft Word’. The steps of this method explain below:
📌Steps:
- First of all, select the entire data list from the Excel worksheet. Press Ctrl+C and Copy the data.
- Now, open Microsoft Word and click on Blank Document.
- Then, press Ctrl+V to paste the data into Word.
- In the Home tab, go to the Font column and select the Change Case.
- After that, click on Capitalize Each Word.
- Again, copy all the data with Ctrl+C.
- Finally, paste the data into the range of cells C5:C14.
As you can see, our method worked correctly to capitalize each word in Excel.
Read More: How to Change Sentence Case in Excel (6 Easy Methods)
Method 6: Apply UPPER Function
In this method, we will use the UPPER function to capitalize word in the Excel worksheet. Using this function will help us to capitalize all letters of a text. The steps are given below.
📌Steps:
- First, Select Cell C5. In this cell, write the following formula:
=UPPER(B5)- Press Enter on your keyboard to get the result.
- Now, double click the Fill Handle sign allocated on the right-bottom side of the cell to copy the formula up to Cell C15.
- Finally, we will get the capitalized word in our desire cells.
Read More: How to Capitalize All Letters Without Formula in Excel (4 Quick Methods)
Method 7: Use REPLACE Function to Capitalize Word in Excel Sheet
The REPLACE function is mainly used for substituting a letter or word in the datasheet. Thus, this function can help us in our case also. The method is given below step by step.
📌Steps:
- Select Cell C5 and write down the following formula as below.
=REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1)))- Press Enter on your keyboard to get the result.
- Now, double click the Fill Handle sign allocated on the right-bottom side of the cell to copy the formula up to Cell C15.
- We will get the word capitalize in our desire cells, however, it only capitalized the first word of the text.
🔎 Breakdown of the Formula
We are doing this breakdown for Cell C5.
👉 LOWER(B5): This function returns mario puzo.
👉 LEFT(B5,1): This function returns m.
👉 UPPER(LEFT(B5,1)): This function returns M.
👉 REPLACE(LOWER(B5),1,1,UPPER(LEFT(B5,1))): Finally, it returns Mario puzo.
Read More: How to Change Upper Case to Lower Case in Excel (5 Methods)
Practice Section
Here, we have provided a practice sheet for you so that you can practice by yourself.
Conclusion
That is the end of this article. I hope that this will be helpful for you and you may capitalize each word in the Excel worksheet. If you have any further queries or recommendations, please share them with us in the comments section below.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
