VBA: How to Count Occurrences of Character in String

You can use the following basic syntax to count the number of occurrences of a character in a string using VBA:

Sub CountOccurrences()
    Dim i As Integer
    
    'Specify character to look for
    my_char = "/"
    
    'Count occurrences in each string in B2:B12 and display results in C2:C12
    For i = 2 To 12
        Count = (Len(Range("B" & i)) - Len(Replace(Range("B" & i), my_char, ""))) / Len(my_char)
        Range("C" & i) = Count
    Next i
End Sub

This particular example counts the number of occurrences of a slash ( / ) in each cell in the range B2:B12 and displays the results in the range C2:C12.

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

Example: Count Occurrences of Character in String Using VBA

Suppose we have the following dataset in Excel that shows the names of various basketball players and the positions they may play in a game:

Suppose we would like to count the number of slashes ( / ) in each string in the Position column.

We can create the following macro to do so:

Sub CountOccurrences()
    Dim i As Integer
    
    'Specify character to look for
    my_char = "/"
    
    'Count occurrences in each string in B2:B12 and display results in C2:C12
    For i = 2 To 12
        Count = (Len(Range("B" & i)) - Len(Replace(Range("B" & i), my_char, ""))) / Len(my_char)
        Range("C" & i) = Count
    Next i
End Sub

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

The values in column C display the number of occurrences of slashes in the corresponding strings in column B.

For example:

• The string Guard /Forward contains 1 slash.
• The string Guard contains 0 slashes.
• The string Guard contains 0 slashes.
• The string Forward / Center contains 1 slash.

And so on.

To count the occurrences of a different character, simply change the character of the my_char variable in the macro.

Additional Resources

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

VBA: How to Check if String Contains Another String
VBA: How to Count Number of Rows in Range
VBA: How to Write COUNTIF and COUNTIFS Functions