How to Count Occurrences in a String Using Excel VBA (4 Methods)

Working with strings is a fundamental aspect of programming, and being able to count occurrences of specific characters or substrings within a string is a useful skill. In this article, we’ll explore different approaches to using VBA (Visual Basic for Applications) to count occurrences in a string, providing examples and explanations along the way.

How to Launch VBA Editor in Excel

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

• Select Insert > Module to open a blank module.

Method 1 – Counting the Number of Occurrences of a Word in a Given String

Let’s say we want to count how many times the word Excel appears in a string. We’ll use VBA to achieve this.

• Open the VBA Editor in Excel:
  • Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
  • Then, click Insert > Module to create a new blank module.

• Enter the VBA Code:

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

• Run the Code:
  • Press F5 to execute the code and see the output.

Method 2 – Counting the Number of Occurrences of a Word in Multiple Strings

Building upon the previous method, if you need to split text data into different cells and count how many times the word “Exceldemy” appears in each cell, the following code can assist 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

• Press the F5 key and see the output below.

Method 3 – Counting the Number of Occurrences of a Slash in a String Using a User-Defined Function

Suppose we have a dataset that shows different football players’ names along with their playing positions. Now, you want to count the number of times each playing position appears. For this case, you can create a user-defined function in VBA to achieve this.

• Create the User-Defined Function:
  • Open your VBA Editor.
  • Copy and paste the following code:

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

• Use the User-Defined Formula:
  • Enter the following formula in cell D5 and press Enter:

• Drag the fill handle from D5 to D13 to get the results for other cells.

Method 4 – Counting Occurrences of a Character in a String Using VBA

In this method, we’ll input a string through an input box and count the occurrence of a specific character within it. Follow these steps:

• Create the VBA Subroutine:
  • 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

• Run the Code:
  • Press F5 to execute the code.
  • An input box will prompt you to enter a string and the character you want to count.
  • The code will display a message box with the result.

Download Practice Workbook

You can download the practice workbook from here:

Frequently Asked Questions

• How to Count Strings in VBA?

  There are multiple ways to count the number of strings in VBA. Here are some possible methods:

  1. Counting Elements in an Array of Strings:
     • You can use the UBound function to determine the number of elements in an array of strings.
  2. Counting Characters in a String:
     • To count the total number of characters in a string, you can utilize the built-in LEN function. It returns the length of the string, which corresponds to the number of characters.
  3. Counting Occurrences of a Substring:
     • If you need to count how many times a specific substring appears within a string, you can use the InStr and InStrRev functions in a loop. These functions help locate occurrences of the substring.

  How to Count Characters in VBA?

  In VBA, you can determine the number of characters in a string using the LEN function. Here’s an example code snippet that counts the characters in a string variable called “Hello World”:

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

  After executing this code, the variable count will contain the total number of characters in the string “Hello World.”

<< Go Back to Count Words | Learn Excel