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

When adding certain information to an Excel sheet, such as company names or employee names, we may want to capitalize the first letter of each word. There may be times when Excel users need to change the case of text in their spreadsheets. We can easily use VBA Macros to capitalize the first letter of each word. In this article, we will demonstrate ideal examples of Excel VBA to capitalize the first letter of each word.

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

Using the keyboard to manually change the contents of the cells would make it simple to do. But even so, while dealing with a lot of data, we could inadvertently input it improperly. There are several ways we can handle the issue. Excel VBA is one of them. If you are familiar with Excel’s VBA programming, you can do lengthy tasks quickly.

To capitalize the first letter of each word, we will use the following dataset. The dataset contains some employee names and their designation. But those texts are not proper cases. We know that the first letter of any name or designation must be capitalized. To do this let’s follow different and quick ways in Excel VBA.

1. VBA Proper Function to Capitalize First Letter of Each Word

The PROPER function transforms the initial character to the upper case and the other characters to the lowercase. We use this function in Excel sheet, we can also use it in Excel VBA. This function in Excel VBA converts user input text to the proper case. It is possible to use it to capitalize each word in a string.

STEPS:

• To begin, open the ribbon and choose Developer from the drop-down menu.
• Then select Visual Basic to open the Visual Basic Editor.
• The Visual Basic Editor may also be accessed by pressing Alt + F11.

• Alternatively, you may right-click the sheet and select View Code from the pop-up menu.

• After that, select Module from the Insert drop-down menu.

• This will take you to the Visual Basic Editor, where you will write your codes.
• Then copy and paste 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

• Further, to save the code in your workbook, click on that save icon or press Ctrl + S. While saving the file, make sure you saved it as Macro enable means the .xlsm file.

• Furthermore, back to the worksheet, and by the same token as before, go to the Developer tab on the ribbon.
• Next, to run the macros click on Macros under the Code group.

• This will appear in the Macro window.
• Now, click on the Run button.

• Select the range of cells that you want to capitalize the first letter of each word. So we select the range $B$5:$C$10.
• And, then click OK.

• And, you can finally see the result.

Read More: How to Change Lowercase to Uppercase in Excel

2. StrConv Function to Capitalize First Letter of Each Word in Excel VBA

The VBA string functions section includes the conversion function called StrConv. The StrConv function in Excel VBA converts a string to uppercase, lowercase, correct case, or Unicode. The Excel built-in function StrConv falls within the String/Text function category. We are using the same dataset for this.

STEPS:

• In the first place, go to the Developer tab from the ribbon.
• Second, click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.
• Alternatively, right-click on the sheet, then select View Code.
• Next, go to Insert and select Module from the drop-down menu.
• And, this will open up the visual basic window.
• After that, copy and paste 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

• Further, press the F5 key or click on the Run Sub button to run the code.

The code doesn’t need to be altered. All you have to do is adjust the range to your needs.

• This will capitalize the first letter of each word.

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

3. Capitalize First Letter of Each Word with VBA UCase Function

Excel VBA has a built-in function that is used to transform an uppercase input text that is sent to it. The UCase function is a comparable function that raises all the lowercase letters in a text string to uppercase. When given a string as input, the VBA UCase function changes all the lower case characters to upper case.

STEPS:

• Firstly, select the range of cells in your worksheet. In our case, we select range B5:C10.

• Secondly, go to the Developer tab from the ribbon.
• Thirdly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
• Instead of doing this, you can right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
• This will appear in the Visual Basic Editor where we write our codes to create a table from range.
• Subsequently, click on Module from the Insert drop-down menu bar.
• This will create a Module in your workbook.
• And, copy and paste the VBA code shown 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

• After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

You don’t need to change the code. All you can do is change the range as per your requirements.

• And, finally, following the steps will capitalize the first word of each word.

Things to Keep in Mind

• You must first display the Developer tab at the top of the screen before you can begin coding in Excel. It is simple to display the Developer tab. Just carry out these actions. Choose File > Options, and Customize Ribbon. Mark Developer with a checkbox.

• The file explorer is located on the left side of the editor. Click ‘ThisWorkbook’ twice in the file directory you’re currently in. Now you can enter and run VBA code in your worksheet using the editor!
• Your Excel file must be saved as a Macro-Enabled Workbook (.xlsm extension). Only files with a .xlsm extension will allow your macro to run.

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above examples will assist you to Capitalize First Letter of Each Word in Excel VBA. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

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