How to Capitalize First Letter of Each Word in Excel

This post will guide you how to capitalize the first letter of each word in a cell in Excel. How do I capitalize the first letter only with a formula or VBA Macro in Excel.

Capitalize the First Letter of Each Word with Formula


Assuming that you have a list of cells with various phrases in range B1:B4 and you only want the first letter of each word is capitalized. How to achieve it. You can use a formula based on the PROPER function to achieve the result. Just like this:

=PROPER(B1)

Type this formula into a blank cell and press Enter key and then drag the AutoFill Handle over to other cells to apply this formula.

capitalize first letter of each word1

You will see that the first letter of each word in cells have been capitalized.

Capitalize the First Letter of Each Word with VBA Code


You can also use an Excel VBA macro to achieve the result of capitalizing the first letter of each word in cells. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

capitalize first letter of each word2

Sub CapitalizeFirstLetter()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select range of cells that you want to capitalize the first letter", "CapitalizeFirstLetter", myRange.Address, Type:=8)
    For Each myCell In myRange
       myCell.Value = Application.Proper(myCell.Value)
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

capitalize first letter of each word3

#6 Select range of cells that you want to capitalize the first letter.

capitalize first letter of each word4

#7 Let’s see the last result:

capitalize first letter of each word5

Video: Capitalize the First Letter of Each Word

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar