VBA – Count the Number of Occurrences of a String or Character within a String

I was trying to help someone in a forum that had a simple question “How can I count the number of time a string/character is found within a string?”

I originally answer by referring them to a procedure written by someone that looped through each character within the string. But this seemed wrong.

So after a few minutes of simple reflection, it hit me that nothing could be simpler! Access/VBA has built-in function to handle this very easily. Below is the procedure I came up with.

'---------------------------------------------------------------------------------------
' Procedure : CountOccurrences
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Count the number of times a string is found within a string
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sText         String to search through
' sSearchTerm   String to count the number of occurences of
'
' Usage:
' ~~~~~~
' CountOccurrences("aaa", "a")               -> 3
' CountOccurrences("514-55-55-5555-5", "-")  -> 4
' CountOccurrences("192.168.2.1", ".")       -> 3
' CountOccurrences("192.168.2.1", "/")       -> 0
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-02-19              Initial Release
' 2         2019-02-16              Updated Copyright
'                                   Updated Error Handler
'---------------------------------------------------------------------------------------
Function CountOccurrences(sText As String, sSearchTerm As String) As Long
    On Error GoTo Error_Handler
 
    CountOccurrences = UBound(Split(sText, sSearchTerm))
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CountOccurrences" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

So, as you can see, by simply combining 2 built-in VBA functions we can quickly and efficiently get our answer. VBA can be so beautiful at times!

What’s even nicer about this solution is that it is not Access specific, so it will work in Access, Excel, Word, … any VBA project.

6 responses on “VBA – Count the Number of Occurrences of a String or Character within a String”

  1. Dejan Mladenovic

    I am new to your blog, so I have been reading a few posts in the past. I like the posts, and this nice function, CountOccurrences .

    It can also be done this way, assuming we do not search for empty string, sSearchTerm = “” :
    CountOccurrences = (Len(sText) – Len(Replace(sText, sSearchTerm, “”))) / Len(sSearchTerm)

    In order to handle sSearchTerm = “”, to avoid division by zero, it gets more comlex, just a bit:
    If Len(sSearchTerm) = 0 Then
    CountOccurrences = 0
    Else
    CountOccurrences = (Len(sText) – Len(Replace(sText, sSearchTerm, “”))) / Len(sSearchTerm)
    End If

    Your function handles empty string well, I believe I will adopt it.

    Thank you 🙂

  2. Dejan Mladenovic

    Using LEN and REPLACE may work as well:
    ? LEN(“514-55-55-5555-5”) – LEN(REPLACE(“514-55-55-5555-5″,”-“,””)

    2. ap2

      If it’s 1 character only. Otherwise you’d need to do something like:
      ? (len(“514-55-55-5555-5”) – len(replace(“514-55-55-5555-5″,”-55″)))/3
      Where 3 is the length of the string you are looking for (“-55” in this case).

      Also, you can specify within the Replace function (in the last argument) the comparison method, same as you can do in the Split function.

  3. ap2

    You can also add an optional argument to make the text comparison binary, text or database:
    Function CountOccurrences(sText As String, sSearchTerm As String, Optional vCompare as vbCompareMethod = vbBinaryCompare) As Long

    And then:
    CountOccurrences = UBound(Split(sText, sSearchTerm, , vCompare))