How to count the number of a specific character in a string in VBA

---

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.