How to Capitalize Each Word in Excel (7 Ways)

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.

Capitalize Each Word in Excel


Method 1: Manually Modify in Formula Bar to Capitalize the 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:

Manually Modify in Formula Bar to Capitalize the First letters of Each Word

πŸ“Œ 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.

Manually Modify in Formula Bar to Capitalize the First letters of Each Word

  • 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.

Manually Modify in Formula Bar to Capitalize the First letters of Each Word

  • 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.

Manually Modify in Formula Bar to Capitalize the First letters of Each Word

πŸ‘‰ 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.

Use PROPER Function to Capitalize Each Word in Excel

  • 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.

Use PROPER Function to Capitalize Each Word in Excel

  • Finally, we will get each word in the block form in our desire cells.

Use PROPER Function to Capitalize Each Word in Excel


Method 3: Use the 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.

  • Now, go to the Data tab and select the From Table option from the Get & Transform.

Use the Power Query Option to Capitalize Each Word

  • A small dialog box will appear entitled Creat Table. Click on My table has headers and finally click the OK.

  • Another dialog box called Power Query Editor will appear. In this box, go to Add Column tab and select the Format.
  • Then, select the option Capitalize Each Word.

Use the Power Query Option to Capitalize Each Word

  • A new column will add and you get the first letter of each word in capital.

Use the Power Query Option to Capitalize Each Word

  • Click on the Close Button sign in the right corner of that dialog. box. Another small box will appear and click on Keep.

  • You will find a new sheet that will open in the Sheet names.

Use the Power Query Option to Capitalize Each Word

  • Click on β€˜Sheet 2’ and you will get the result.

Use the Power Query Option to Capitalize Each Word

Thus, we can say that our function worked smoothly.

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


Method 4: Using VBA Code 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.

Using VBA Code to Capitalize Each Word of A Line in Excel

  • A dialog box will appear.
  • From the Insert tab on that box, click Module.

Using VBA Code to Capitalize Each Word of A Line in Excel

  • Then, write down the following visual code in that empty editor box.
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.

  • A new dialog box will appear. Select capitalize_each_word.

  • Click on the Run button to run this code and the result will appear.

Using VBA Code to Capitalize Each Word of A Line in Excel

In the end, we can say that our visual code worked perfectly and we got the result at our desired cells.


Method 5: Using 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.
  • Press Ctrl+V to paste the data into Word.
  • In the Home tab, go to the Font column and select the Change Case.
  • Click on Capitalize Each Word

Using Microsoft Word to Capitalize Each Word of A String in Excel

  • Again, select all the data with Ctrl+C and paste the data into the range of cells C5:C14.

Using Microsoft Word to Capitalize Each Word of A String in Excel

As you can see, our method worked correctly to capitalize each word in Excel.


Method 6: Applying 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.

Applying UPPER Function to Capitalize Each Word

  • Now, double click the Fill Handle sign allocated on the right-bottom side of the cell to copy the formula up to Cell C15.

Applying UPPER Function to Capitalize Each Word

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: Using the 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.

Using the REPLACE Function to Capitalize Word in Excel Sheet

  • 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.

Using the REPLACE Function to Capitalize Word in Excel Sheet

πŸ”Ž 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.


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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo