Excel VBA: Count Occurrences in a String

VBA & macros · Updated November 21, 2026

To count how many times one piece of text appears in another in Excel VBA, search repeatedly with InStr, advancing past each match. The function below counts non-overlapping matches by default, with optional settings for overlapping matches and case-insensitive searches.

A reusable VBA function

Put this function in a standard VBA module. It returns zero if either input is empty, and otherwise returns the number of matches it finds.

Option Explicit

Public Function CountOccurrences(ByVal SourceText As String, _
                                 ByVal SearchText As String, _
                                 Optional ByVal Overlap As Boolean = False, _
                                 Optional ByVal IgnoreCase As Boolean = False) As Long
    Dim startAt As Long
    Dim foundAt As Long
    Dim compareMode As VbCompareMethod

    ' An empty search string is treated as zero occurrences.
    If Len(SourceText) = 0 Or Len(SearchText) = 0 Then Exit Function

    If IgnoreCase Then
        compareMode = vbTextCompare
    Else
        compareMode = vbBinaryCompare
    End If

    startAt = 1
    Do
        foundAt = InStr(startAt, SourceText, SearchText, compareMode)
        If foundAt = 0 Then Exit Do

        CountOccurrences = CountOccurrences + 1
        If Overlap Then
            startAt = foundAt + 1
        Else
            startAt = foundAt + Len(SearchText)
        End If
    Loop
End Function

To add it in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. On a Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. You can also open the editor from the Developer tab by choosing Visual Basic. Save a copy of the workbook before running or changing macros: VBA actions cannot be undone with Ctrl+Z. A workbook containing VBA should be saved as an Excel Macro-Enabled Workbook (.xlsm).

How the InStr loop counts matches

InStr returns the position of the first match it finds, or zero if there is no match. In this function, the first search begins at character 1. After a match, the loop adds one to the count and starts the next search at a later position. It stops when InStr returns zero. The loop therefore searches the whole string rather than just checking whether the text appears at least once.

For example, searching for "is" in "This is simple" finds two matches. The first begins in “This”; the second is the separate word “is.” A function based on only one call to InStr would report the first position, not the total count. Repeating the search is what turns a find operation into a count.

The function uses Len(SearchText) to determine how far to advance for the usual non-overlapping count. If the search text is three characters long, it resumes three positions after the match. That prevents the same characters from being counted again as part of a second match.

Overlapping and non-overlapping matches

Decide what “occurrence” means for your task. In normal text counting, matches are usually non-overlapping: each character can belong to only one counted match. In some pattern or sequence checks, a later match may begin inside the previous one, so overlapping matches matter.

  • With source text "aaaa" and search text "aa", the default call returns 2: the matches occupy positions 1–2 and 3–4.
  • With the same strings and Overlap set to True, the function returns 3: matches begin at positions 1, 2, and 3.
  • With source text "banana" and search text "ana", the default is 1. With overlap enabled, it is 2, because the matches begin at positions 2 and 4.

The overlapping option works by advancing the next search one character from the previous match’s starting position. The default advances by the length of the search text instead. This distinction is important: a count can be correct under one definition and wrong under the other. Choose the rule that fits the data before comparing totals.

Use the function in a worksheet cell

Because the function is declared Public in a standard module, it can be called as a worksheet user-defined function after macros are enabled. With the full text in A1 and the text to find in B1, enter =CountOccurrences(A1,B1). If A1 contains banana and B1 contains ana, the result is 1. To count overlapping matches with the same cell layout, use =CountOccurrences(A1,B1,TRUE); the result is 2.

The fourth argument controls case sensitivity. With source text in A1 and search text in B1, =CountOccurrences(A1,B1,FALSE,TRUE) ignores case and still uses non-overlapping matches. For instance, it treats "Cat cat CAT" as three matches for "cat". The third argument is set to FALSE so the fourth argument is in the correct position.

A VBA user-defined function calculates when Excel recalculates the cell, like other worksheet functions. It does not change the source text. If the function name appears as #NAME?, check that the code is in a standard module, the workbook is saved as .xlsm, and macros are enabled for that trusted workbook. Excel for the web cannot run VBA; use desktop Excel to calculate VBA functions. Do not enable macros in a workbook from an untrusted source merely to make a function work.

Case sensitivity and text comparison

The function makes comparison behavior explicit. Its default, vbBinaryCompare, distinguishes uppercase and lowercase letters: "A" and "a" are different. Set IgnoreCase to True to use vbTextCompare, which performs a text comparison that ignores case. The comparison follows the system’s text-comparison rules, so it is not a promise of language-independent Unicode normalization.

This is useful when data comes from inconsistent sources. Names, product labels, and manually entered notes may vary in capitalization even when the intended text is the same. Conversely, a case-sensitive count can be useful for codes where capitalization carries meaning. Choose one behavior deliberately, and use it consistently when comparing results across rows or files.

Neither comparison mode removes spaces, punctuation, or accents. A search for "cat" does not find " cat" as a whole phrase with the leading space included, and a search for one accented spelling does not automatically match another. If cleanup is required, do it separately and consistently rather than assuming InStr normalizes the text.

Counting characters versus counting a phrase

The same loop can count one character or a multi-character phrase. For a single character, such as counting commas in a cell, the search text has length one, so the overlapping and non-overlapping results are identical. For a longer phrase, the choice about overlap can change the answer. This is why a function that only counts a particular character may not be suitable for every search task.

If you do not need VBA, a worksheet formula can count non-overlapping matches. With the source string in A1 and the search text in B1, use =IF(B1="",0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)). If A1 contains banana and B1 contains ana, the result is 1: replacing the match removes three characters, and three divided by the search text’s length of three is one. This formula is case-sensitive because Excel’s SUBSTITUTE function is case-sensitive, and it does not count overlapping matches. The VBA function offers a direct way to select both behaviors.

Common problems and practical checks

Counts that seem unexpectedly low or high often come from the search rule or the exact cell contents, not a failure of the loop. Check these points before changing the code:

  • Empty search text: The function deliberately returns zero. Mathematically, an empty string can be discussed in other ways, but treating it as zero avoids a misleading result or an endless search.
  • Overlaps: A pattern such as "aa" in "aaaa" produces different answers under the two valid counting rules. Set the third argument to TRUE only when overlapping matches are intended.
  • Case and spaces: A trailing space in a cell is a real character. Likewise, the default comparison distinguishes letter case. Inspect the actual cell contents if a result does not match what is visible.
  • Wrong module or file type: Put a worksheet-callable function in a standard module, not a worksheet or ThisWorkbook code module. Save code in an .xlsm file rather than a macro-free .xlsx file.
  • Different Excel environment: VBA does not execute in Excel for the web. Open the workbook in desktop Excel to use the macro or recalculate the custom function.

When the source is generated by other systems, consider whether it can contain line breaks or nonbreaking spaces. Those characters may be hard to notice in a cell, but they remain part of the string. The function searches for the exact sequence supplied; it does not automatically trim the text or replace special spaces. If needed, normalize the source and search term using a separate, deliberate cleanup step.

Choosing between a macro, a cell function, and a formula

Use a worksheet UDF when you want a count beside each source value and need the same logic to recalculate as cells change. Use a regular VBA macro when the task is a one-time operation across a range, such as writing counts into another column or compiling a report. A macro can edit workbook data, while the function above only returns a value; that difference affects both safety and how users should interact with the workbook.

For a small, straightforward count that does not need overlap handling, the formula alternative may be easier to share because it does not require VBA or macro permissions. VBA is more flexible when you need a reusable routine, a case-insensitive option, or the ability to use the same logic in a larger procedure. For many thousands of cells, avoid repeatedly scanning very long strings in a slow, cell-by-cell macro if performance becomes an issue; calculate in memory where practical, and test on a copy before changing a large workbook.

VBA code can be saved in workbooks and in other macro-enabled locations, but this example is stored in the workbook’s standard module. Sharing the workbook does not guarantee that another person’s Excel will run the code: their macro security settings and the Excel platform matter. Explain the function’s arguments to colleagues, and provide a formula or a plain-value result when the recipient cannot use desktop Excel with macros enabled.

Frequently Asked Questions

How do I count a character in a string with VBA?
Pass that character as the search text to CountOccurrences. For example, with source text "a,b,c" and search text ",", the function returns 2. A one-character search cannot overlap itself.

Does InStr count every occurrence automatically?
No. One call to InStr returns the position of one match. To count all matches, call it repeatedly, increment a counter, and advance the starting position after each result.

How can I count matches without distinguishing uppercase and lowercase?
Use the function’s fourth argument: CountOccurrences(SourceText, SearchText, False, True). The third argument keeps matching non-overlapping; the fourth requests case-insensitive text comparison.

Can I count overlapping matches?
Yes. Set the third argument, Overlap, to True. For example, "aaaa" contains three overlapping matches of "aa", compared with two non-overlapping matches.

Why does my VBA count function show #NAME? in Excel?
Check that the function is in a standard module in the workbook, that the workbook is macro-enabled and saved as .xlsm, and that macros are enabled for a trusted file. Excel for the web cannot run VBA functions.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.