Using Excel VBA to Capitalize the First Letter of Each Word

The dataset contains employee names and their designation.

To capitalize the first letter of each word:

Example 1 – Using the VBA Proper Function to Capitalize the First Letter of Each Word

STEPS:

Go to Developer.
Select Visual Basic to open the Visual Basic Editor. You may also press Alt + F11…

or right-click the sheet and select View Code.

Select Module in Insert.

Enter the following VBA code.

VBA Code:

Sub CapitalizeEachWord()
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select Range", "CapitalizeFirstWord", rng.Address, Type:=8)
    For Each myCell In rng
       myCell.Value = Application.Proper(myCell.Value)
    Next
End Sub

To save the code in your workbook, click save or press Ctrl + S. Make sure you save it as a Macro enabled .xlsm file.

Go back to the worksheet and go to the Developer tab.
To run the macros click Macros in Code.

In the Macro window, click Run.

Select the range of cells you want to capitalize the first letter of each word. Here, $B$5:$C$10.
Click OK.

This is the output.

Example 2 – Using the StrConv Function to Capitalize the First Letter of Each Word in Excel VBA

STEPS:

Go to the Developer tab.
Click Visual Basic to open the Visual Basic Editor or press Alt + F11. You cal also right-click the sheet and select View Code.
Go to Insert and select Module.
In the visual basic window, enter the VBA code below.

VBA Code:

Sub CapitalizeFirstWord1()
  Dim rng As Range
  For Each rng In Range("G5:G10")
    rng.Value = StrConv(rng.Value, vbProperCase)
  Next rng
End Sub

Press F5 key or click Run Sub to run the code.

This will capitalize the first letter of each word.

Example 3 – Capitalize the First Letter of Each Word with the VBA UCase Function

STEPS:

Select B5:C10.

Go to the Developer tab.
Click Visual Basic to open the Visual Basic Editor or press Alt + F11. You cal also right-click the sheet and select View Code.
Go to Insert and select Module.
In the visual basic window, enter the VBA code below.

VBA Code:

Sub CaptalizeFirstWord2()
For Each cell In Selection.Cells
  wrd = Split(cell.Value)
  For i = LBound(wrd) To UBound(wrd)
    wrd(i) = UCase(Left(wrd(i), 1)) & Mid(wrd(i), 2)
  Next i
  cell.Value = Join(wrd)
Next cell
End Sub