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
- Capitalize the First Letter of Each Word with VBA Code
- Video: Capitalize the First Letter of Each Word
Table of Contents
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:
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.
You will see that the first letter of each word in cells have been capitalized.
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.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
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.
#6 Select range of cells that you want to capitalize the first letter.
#7 Let’s see the last result: