How to Convert Strings to Proper Case Using VBA


A string is in proper case if the first letter of each word in the string is capitalized and every other letter in each word is lower case.

You can use the following syntax in VBA to convert a range of cells with strings to proper case:

Sub ConvertToProperCase()
    
    Dim i As Integer

    For i = 2 To 10
        Range("B" & i) = StrConv(Range("A" & i), vbProperCase)
    Next i
    
End Sub

This particular example will convert each string in the range A2:A10 to proper case and display the results in the range B2:B10.

The following example shows how to use this syntax in practice.

Example: Convert Strings to Proper Case Using VBA

Suppose we have the following column of strings in Excel:

Suppose we would like to convert each string in column A to proper case and display the results in column B:

We can create the following macro to do so:

Sub ConvertToProperCase()
    
    Dim i As Integer

    For i = 2 To 10
        Range("B" & i) = StrConv(Range("A" & i), vbProperCase)
    Next i
    
End Sub

When we run this macro, we receive the following output:

VBA convert strings to proper case

Column B displays each string in column A in proper case.

Note: You can find the complete documentation for the StrConv function in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Compare Strings in VBA
How to Remove Spaces from String in VBA
How to Replace Characters in String in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *