Excel VBA to Count Occurrences in String (4 Suitable Examples)

Working with strings is a fundamental aspect of programming, and being able to count occurrences of a specific character or substring within a string is a useful skill to have. specific patterns in text. In this article, we will explore different approaches to using VBA to Count Occurrences in String, providing examples and explanations along the way.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

• Press Alt + F11 to open your Microsoft Visual Basic.

• Then press Insert > Module to open a blank module.

VBA to Count Occurrences in String: 4 Different Cases in Excel

Counting occurrences in a string is important and hard to deny their necessity in a practical context. For instance, If you are building an application that requires a search functionality, counting the occurrences of a search term in a string can be essential. This will help you provide users with relevant search results. In this article, we will explore four different scenarios to count occurrences in a string.

1. Count Number of Occurrences of Word in a Given String

In this example, we will take a string from the worksheet and count how many times the “Excel” word appears.

Sub Word_Occurrences_Count()
    'Set the string to search and the substring to find
    myString = Cells(5, 2).Value
    searchSubstring = "Excel"
    'Loop through the string, counting occurrences of the substring
    count = 0
    pos = InStr(1, myString, searchSubstring)
    Do While pos > 0
        count = count + 1
        pos = InStr(pos + 1, myString, searchSubstring)
    Loop
    'Display the result
    MsgBox "The substring '" & searchSubstring & "' appears " & count & " times in the string."
End Sub

Now press on the F5 key and see the output as given below.

2. Counting Number of Occurrences of Word in Multiple String

Following the previous example, if you want to split some text data into different cells and count how many times the “Exceldemy“ appears in each cell, then this code might aid you.

Sub Occurrence_Count_Multiple_String()
    For Each cell In Range("B5:B9")
        count = 0
        pos = InStr(1, cell.Value, "Exceldemy")
        Do While pos > 0
            count = count + 1
            pos = InStr(pos + 1, cell.Value, "cat")
        Loop
        MsgBox "The substring 'Exceldemy' appears " & count & " times in cell " & cell.Address & "."
    Next cell
End Sub

All good! Hit the F5 key and see your output below.

3. Number of Occurrences of Slash from a String Through User-Defined Function

Suppose we have the dataset given above that shows different football players’ names along with their playing position. Now you want to count their playing role. For this case, you can use the following code to make a user-defined function and count the playing position later.

• To begin with, copy the following code in your VBA Editor.

Function Occurrence_Count(istring As String)
    iResult = Len(istring) - Len(Replace(istring, "/", ""))
    Occurrence_Count = iResult + 1
End Function

The given code defines a VBA function named Occurrence_Count that takes a string argument istring and returns the count of occurrences of a specific character (“/” in this case) in the given string.

• Write the following user-defined formula in the D5 cell and press enter afterward.

• Drag the Fill Handle from D5 to D13 to get the other result.

4. Count Occurrences of Character in String with VBA

In the last example, we will input a string through the input box and count the occurrence of a specific character within it. To do so, copy and paste the following code into your VBA editor.

Sub Character_Count_in_String()
    myString = InputBox("Drop a String Here")
    subString = InputBox("Which Character you would Like to Count")
    count = 0
    pos = InStr(1, myString, subString)
    Do While pos > 0
        count = count + 1
        pos = InStr(pos + 1, myString, subString)
    Loop
    If count >= 1 Then
        MsgBox "The substring '" & subString & "' appears " & count & " times in the " & Chr(39) & myString & Chr(39)
    Else
        MsgBox " There is no '" & subString & "' in " & Chr(39) & myString & Chr(39)
    End If
End Sub

Now press on the F5 key and see the output as given below.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

Conclusion

In this article, we have listed and tried to give some practical examples of VBA to count occurrences in a string. As you have already understood, there is plenty of ways to do this task. However, If you have any queries, feel free to comment below and we will get back to you soon.

Frequently Asked Questions

• How do you count strings in VBA?

There are multiple ways to count the number of strings in VBA. Some of the possible ways are listed below.

• Count the number of elements in an array of strings using the UBound function.
• Count the number of characters in a string using the LEN function.
• Count the number of occurrences of a substring in a string using the InStr and InStrRev functions in a loop.

• How do you count characters in VBA?

In VBA, you can count the number of characters in a string using the built-in LEN function. The LEN function returns the length of a string, which is the number of characters in the string.

Here’s an example code that counts the number of characters in a string variable called “Hello World“.

Dim myString As String
Dim count As Long
count = Len(Hello World)

After this code is executed, the variable count will contain the number of characters in the string “Hello World“.

<< Go Back to Count Words | Learn Excel