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

overview image of vba count occurrences in string

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.

opening of VBA editor in Excel worksheet

  • Select Insert and click on Module to open a blank module.

Vba Editor interface


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

Input data for counting the Number of Occurrences of 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.
    • Click Insert and select Module to create a new blank module.
  • Enter the VBA Code:

Code for counting Number of Occurrences of Word in a Given String

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

 Code Breakdown:

  • myString = Cells(5, 2).Value: This line sets the variable myString to the value of cell B5.
  • searchSubstring = “Excel”: This line sets the variable searchSubstring to the string Excel.
  • pos = InStr(1, myString, searchSubstring): This line finds the position of the first occurrence of searchSubstring within myString. If no occurrence is found, pos is set to 0.
  • Do While pos > 0: This line begins a loop that will continue as long as a match for searchSubstring is found.
  • count = count + 1: This line increments the count variable to record the occurrence of searchSubstring.
  • pos = InStr(pos + 1, myString, searchSubstring): This line finds the position of the next occurrence of searchSubstring within myString, starting from the position after the previous match. If no further match is found, pos is set to 0.
  • MsgBox “The substring ‘” & searchSubstring & “‘ appears ” & count & ” times in the string.”: This line displays a message box with the number of occurrences of searchSubstring found in myString.
  • 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

Input data for counting Number of Occurrences of Word in Multiple String

Building onthe 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:

Code for counting Number of Occurrences of Word in Multiple String

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

 Code Breakdown:

  • For Each cell In Range(“B5:B9”): This loop iterates through each cell in the range B5:B9 using a For Each loop.
  • pos = InStr(1, cell.Value, “Exceldemy”: This line uses the InStr function to find the position of the substring “Exceldemy” within the cell value. If the substring is found, the code enters a Do While loop.
  • The Do While loop increases the count variable by 1 for each occurrence of the substring in the cell value.
  • The Do While loop continues searching for occurrences of the substring in the cell value until no more occurrences are found.
  • The code displays a message box for each cell, showing the number of occurrences of the substring Exceldemy in that cell. The message box includes the cell address.
  • 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

Input data for counting Number of Occurrences of Slash from a String

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:

Code for counting Number of Occurrences of Slash from a String

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:
=Occurrence_Count(C5)

Write the user-defined function

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

Drag the fill handle tool


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:

Code for Counting Occurrences of Character in String

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

Code Breakdown:

  • The Sub statement indicates the start of a subroutine called Character_Count_in_String.
  • The InputBox function takes a string (myString) and a character to search for (subString).
  • pos = InStr(1, myString, subString): search for the subString in myString, starting at the first character (position 1). If the subString is found, it returns the position of the first occurrence of the subString to the pos variable or returns 0 otherwise.
  • A Do While loop is used to continue searching for the subString in myString until it is no longer found. If pos is greater than 0 (i.e. if the subString is found), then the count variable is incremented by 1 and the pos variable is set to the position of the next occurrence of the subString.
  • If count is greater than or equal to 1, a message box is displayed indicating how many times the subString was found in the myString. If count is 0, a message box is displayed indicating that the subString was not found in the myString.
  • The End Sub statement indicates the end of the subroutine.
  • 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

Get FREE Advanced Excel Exercises with Solutions!
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo