How to Capitalize Each Word in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

capitalize each word in excel


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.

Dataset to Capitalize Each Word in Excel


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.

Dataset Modifying Manually

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

Copying Words From Cell B5

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

Pasting Copied Words in Cell C5

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

Changing m to M

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

Changing p to Capital P

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

Final Result from Modifying Manually

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

Using Excel PROPER 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. Besides it, you can also simply drag the Fill Handle square box up to Cell C15.

Dragging Fill Handle to Copy Formula

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

Final Output from PROPER Function


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.

Using From Table Option to Capitalize Each Word in Excel

  • Afterward, a small dialog box will appear entitled Create Table. Click on My table has headers and finally click the OK.

Create Table Dialog Box

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

Using Power Query Editor to Capitalize Each Word in Excel

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

Getting New Column with Desired Output

  •  Consequently, Another small box will appear and click on Keep.

Power Query Editor Box

  • After that, you will find a new sheet that will open in the Sheet names.
  • Click on ‘Table1’ and you will get the result.

Result from Power Query

  • Finally, we modified the format of the sheet and this is how it looks.

Table after Formatting

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.

Opening Visual Basic Editor

  • Afterward, a new window will appear.
  • From the Insert tab, click Module.

Inserting Module

  • Then, write down the following visual code in that Module.

Writing VBA Code to Capitalize Each Word in Excel

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.

Using View Macros Feature

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

Running Macros to Capitalize Each Word in Excel

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

Final Output from VBA Code


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.

Copying Range

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

Using MS Word to Capitalize Each Word in Excel

  • Again, copy all the data with Ctrl+C.

Copying Data from MS Word

  • Finally, paste the data into the range of cells C5:C14.

Pasting Copied Data in Excel

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.

Applying UPPER 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.

Using Fill Handle

  • Finally, we will get the capitalized word in our desire cells.

Final Result from UPPER Function

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.

Use of REPLACE 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.
  • We will get the word capitalize in our desire cells, however, it only capitalized the first word of the text.

Final Output from REPLACE Function

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

Practice Sheet


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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo