Using Excel VBA to Capitalize the First Letter of Each Word – 3 Examples

Excel VBA to Capitalize First Letter of Each Word: 3 Ideal Examples

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

The PROPER function transforms the initial character to upper case and the other characters to lowercase.

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.

Read More: How to Change Lowercase to Uppercase in Excel

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

Use the StrConv function. It converts a string to uppercase, lowercase, correct case, or Unicode.

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.

Read More: How to Make First Letter of Sentence Capital in Excel

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

The UCase function transforms all the lower case characters to upper case.

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

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

• This will capitalize the first letter of each word.

Things to Keep in Mind

• Enable the Developer tab: Choose File > Options, and Customize Ribbon. Check Developer.

Download Practice Workbook

Download the workbook and practice.

Related Articles

• How to Change Lowercase to Uppercase in Excel Without Formula
• How to Change Sentence Case in Excel
• How to Change Case for Entire Column in Excel
• How to Change Case in Excel Sheet
  
• Change Upper Case to Lower Case in Excel

<< Go Back to Change Case | Text Formatting | Learn Excel