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.

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

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

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

#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

Sub CapitalizeFirstLetter()
    Dim rng As Range
    Dim cell As Range
    
    ' Ask user to select the range
    Set rng = Application.InputBox("Select the range of cells", "Capitalize First Letter", Type:=8)
    
    ' Check if a range was selected
    If rng Is Nothing Then
        MsgBox "No cells were selected.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each cell in the selected range
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            ' Capitalize the first letter and keep the rest of the text unchanged
            cell.Value = Replace(cell.Value, cell.Value, 1, 1, UCase(Left(cell.Value, 1)))
        End If
    Next cell
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:

3. Video: Capitalize the First Letter of Each Word

This Excel video tutorial where we’ll explore two methods to capitalize the first letter of each word in a cell. We’ll start by using a formula based on the PROPER function, followed by a method involving VBA code.