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
Overlapset toTrue, 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 toTRUEonly 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
.xlsmfile rather than a macro-free.xlsxfile. - 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
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Multiline Cell Into Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Data Types: Choosing the Right Variable for MacrosExcel VBA data types define what kind of value a variable can hold, such as a whole number,…
- TG Macro: How to Record and Run Macros on Android SafelyTo make a macro with TG Macro, open the Android app, grant only the permissions it needs, record…
- Excel VBA Set Cell Color by Hex CodeTo set an Excel cell’s fill color from a hex value such as #1E90FF, convert its red, green,…
- VBA Open UserForm: How to Show a Form in ExcelTo open a VBA UserForm in Excel, call its Show method: for example, frmCustomer.Show. Replace frmCustomer with the…
- Set Workbook and Worksheet in VBA: How It WorksIn Excel VBA, use Set to assign a workbook or worksheet object to a variable, then refer to…
- Excel VBA Data Validation Drop-Down Lists: How to Build ThemTo create an Excel data-validation drop-down list with VBA, apply the xlValidateList validation type to the target cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.