More often than not, to save time or for the sake of laziness, people enter data in all lowercase or all uppercase. Transforming this type of data into the proper format can be a serious headache. Let us show you how to make Excel capitalize first letter using VBA to do this automatically.

How to make Excel capitalize first letter

Both Excel and VBA have functions that can do this.

• Proper (Excel)
• StrConv (VBA)

In this example, we will be using the StrConv function in VBA. StrConv function can convert a string into a specified form. This form can be the case of letters, as well as their coding structure (i.e. Unicode or not). The function uses 2 arguments - the string itself, and the conversion type. Choose vbProperCase to convert a string of letters to the proper case. Below is an example.

StrConv("CONVERT ME",vbProperCase)

This code returns "Convert Me" as a string.

To use StrConv with all cells in a selection or a specified range, we need to check every cell using a loop. For Each…Next loop is ideal for moving from cell to cell regardless of selection range dimensions. The idea is to apply the StrConv function to each cell value and replace it.

You can use codes in two ways:

• Module
• Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

Convert selection to proper case

Module Version:

Sub ConvertProperCaseInSelection()

  Dim rng As Range

  For Each rng In Selection

    rng.Value = StrConv(rng.Value,vbProperCase)

  Next rng

End Sub

Immediate Version:

For Each rng In Selection: rng.Value = StrConv(rng.Value,vbProperCase): Next rng

Convert a specified range to proper case

Module Version:

Sub ConvertProperCaseInRange()

  Dim rng As Range

  For Each rng In Range("B2:E11")

    rng.Value = StrConv(rng.Value,vbProperCase)

  Next rng

End Sub

Immediate Version:

For Each rng In Range("B2:E11"): rng.Value = StrConv(rng.Value,vbProperCase): Next rng

Convert entire worksheet to proper case

Module Version:

Sub ConvertProperCaseInWorksheet()

  Dim rng As Range

  For Each rng In ActiveSheet.UsedRange

   rng.Value = StrConv(rng.Value,vbProperCase)

  Next rng

End Sub

Immediate Version:

For Each rng In ActiveSheet.UsedRange: rng.Value = StrConv(rng.Value,vbProperCase): Next rng