Count Sub-strings in an Excel Cell with a User Defined Function

Need to count sub-strings in an Excel cell? It happens all the time: a cell holds comma-separated ticket numbers, a semicolon-separated list of email IDs or a string of codes split by slashes, and you need to know how many items it contains. Excel has no built-in function that does this in one step, so in this tutorial you will create your own: a small user defined function (UDF) in VBA called Sub_String_Count. You give it the text and the separator, and it returns the count. It even works with separators of two or more characters, such as || or a comma followed by a space.

⬇️  Download the Sub-string Count Workbook
Single-sheet workbook · .xlsm (macro-enabled) · Excel 2010 and later

Watch the Video Tutorial

In the video PK writes the function from scratch, tests it with a breakpoint and the Immediate window, and fixes the wrong count that appears with two-character separators.

What You Will Build

• A custom worksheet function =Sub_String_Count(text, separator) that you can use like any built-in function.
• Support for any separator: slash, semicolon, caret, double pipe or comma plus space.
• A zero result for empty cells instead of a misleading 1.
• A clear understanding of the logic, checked step by step in the Immediate window.
• Two formula-only alternatives for when you cannot use macros (added in this refresh).

The Sample Data

The download file has one sheet with the text in column D, the separator in column E and the formula in column F:

F5 contains =Sub_String_Count(D5,E5) and is copied down to F9. You can also type the separator straight into the formula, for example =Sub_String_Count(D9,"^"), which also returns 8.

Part 1 – Create the User Defined Function

Step 1: Open the Visual Basic Editor and insert a module

Go to Developer → Visual Basic or press Alt+F11 to open the Visual Basic Editor. Then choose Insert → Module (or press Alt+I+M). A user defined function must sit in a normal module like this one, not in a sheet module, otherwise Excel cannot see it from a cell.

Step 2: Paste the code

This is the function from the download file:

Option Explicit

Function Sub_String_Count(str As String, Separator As String)

Application.Volatile

If Len(str) = 0 Then
    Sub_String_Count = 0
Else
    Sub_String_Count = (Len(str) - Len(Replace(str, Separator, ""))) / Len(Separator)
    Sub_String_Count = Sub_String_Count + 1
End If

End Function

Step 3: Save as a macro-enabled workbook

Save the file as Excel Macro-Enabled Workbook (.xlsm). If you save it as .xlsx, Excel removes the code and every formula that uses the function turns into #NAME?.

Step 4: Use it in the sheet

In F5 type =Sub_String_Count(D5,E5) and press Enter. Excel shows 6. Copy the formula down to F9 and you get the counts from the table above. The function also appears in the formula AutoComplete list as you type, just like a built-in one.

Part 2 – How the Sub-string Count Formula Works

The trick is to count the separators, not the items. Remove every separator from the text and see how much shorter it gets.

The basic idea

• Len(str) is the length of the full text. For 545/545/58664/4574/566/1234 that is 27.
• Replace(str, Separator, "") removes every separator, giving 5455455866445745661234.
• Len(...) of that text is 22.
• 27 minus 22 is 5, so the text contains 5 separators.
• Five separators split the text into 6 parts, which is why the function adds 1.

In the video, PK first wrote the formula without the + 1, got 5 instead of 6, and realised the result was the number of separators. Adding 1 turns it into the number of sub-strings.

Why the code divides by Len(Separator)

Without the division, the double-pipe and comma-plus-space rows return 11 instead of 6. Each separator in those rows is two characters long, so removing them shortens the text by twice as much. Take the names row:

• The full text PK, Raj, Prince, Jack, Ace, Lucy is 32 characters long.
• Without the five comma + space separators it is 22 characters long.
• 32 minus 22 is 10 characters, but each separator is 2 characters, so 10 / 2 = 5 separators.
• 5 + 1 = 6 names.

Dividing by Len(Separator) makes the function work for a separator of any length. For one-character separators it divides by 1, so nothing changes.

Why the code checks for an empty cell

An empty cell has no separators, so the formula would return 0 + 1 = 1. The If Len(str) = 0 Then test returns 0 instead, which is the honest answer for a blank cell.

Check it yourself in the Immediate window

To see these numbers live, click in the line that calculates the count and press F9 to set a breakpoint. Go back to Excel and re-enter the formula; the code stops at the breakpoint. Press Ctrl+G to open the Immediate window and type ?Len(str) or ?Replace(str, Separator, "") and press Enter to print each value. Press F5 to continue and F9 again to remove the breakpoint.

What Application.Volatile does

Application.Volatile tells Excel to recalculate the function every time anything on the sheet recalculates, which is what the video uses so the result always refreshes. Because both inputs are passed as cell references, the function also updates when D5 or E5 changes without it, so in a very large workbook you can delete that line to save recalculation time.

Formula-Only Alternatives (Addition)

These alternatives are not in the original file. Use them if macros are blocked where you work.

Any Excel version: LEN and SUBSTITUTE

The same logic works with worksheet functions. SUBSTITUTE plays the role of VBA’s Replace:

=IF(LEN(D5)=0,0,(LEN(D5)-LEN(SUBSTITUTE(D5,E5,"")))/LEN(E5)+1)

Microsoft 365 and Excel 2024: TEXTSPLIT

TEXTSPLIT splits the text into one cell per item, and COLUMNS counts them:

=IF(D5="",0,COLUMNS(TEXTSPLIT(D5,E5)))

Both formulas return the same counts as the UDF for the sample data. The UDF is still handy because it is short, easy to read and works in every version that runs macros.

Common Errors and Fixes

#NAME? instead of a number

Excel cannot find the function. Enable macros when you open the file, make sure the code is in a normal module (not in a sheet or ThisWorkbook module), check the spelling of the function name and keep the file as .xlsm.

#VALUE! when the separator cell is empty

With an empty separator, Len(Separator) is 0 and the division fails, so the cell shows #VALUE!. Fill in the separator, or add a line such as If Len(Separator) = 0 Then Exit Function before the calculation; the function then returns 0 instead of an error.

The count is 1 instead of the real number

The separator does not match the text exactly. In the names example the separator is a comma followed by a space; if you enter only a comma in one place and comma plus space in the other, nothing is found. Replace is also case-sensitive for letters, so AND and and are different separators.

The count is one too high

The text probably ends (or starts) with a separator, such as A;B;C;. The function counts the empty item after the last separator. Remove the extra separator from the source text first, or subtract 1 for those rows.

Tips

• Keep the separator in its own cell, as in the download file, so you can change it without editing formulas.
• To use the function in every workbook, store it in your Personal Macro Workbook (call it as PERSONAL.XLSB!Sub_String_Count) or save it in an add-in (.xlam).
• Count email IDs, tags or ticket numbers in a whole column by copying the formula down and adding a SUM at the bottom.
• Microsoft explains the basics of creating custom functions in Excel, and the VBA Replace function reference lists its optional arguments.

Want It Ready-Made?

If you would rather work with finished macro tools than write every function yourself, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles 8 working Excel VBA applications with editable VBA source you can study and reuse. You can also browse all Excel VBA tools.

Related Tutorials

• User Defined Functions in Excel VBA
• UDF to Get the Frequency of a Specific Word in a Paragraph
• UDF to Fetch Comment Text in an Excel Cell
• UDF to Evaluate a Formula in Excel
• How to Use the TEXTSPLIT Function in Excel

Frequently Asked Questions

How do I count sub-strings in an Excel cell?

Count the separators and add 1. The Sub_String_Count function in this tutorial does that: it compares the length of the text with and without the separator, divides the difference by the separator length and adds 1.

Can I count items separated by two characters, like a comma and a space?

Yes. The function divides the length difference by the length of the separator, so a two-character separator such as comma plus space or a double pipe gives the correct count.

Why does the function return 0 for a blank cell?

Because the code checks the length of the text first. An empty cell has length 0, so the function returns 0 instead of 1.

Can I do this without VBA?

Yes. A worksheet formula with LEN and SUBSTITUTE follows the same logic, and in Microsoft 365 you can count the columns returned by TEXTSPLIT. Both formulas are shown in the Formula-Only Alternatives section.

Why do I see #NAME? after opening the file?

Macros are disabled or the file was saved as .xlsx, so Excel cannot find the function. Save the workbook as .xlsm and enable macros when you open it.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To count sub-strings in an Excel cell, you only need one idea: remove the separators, measure how much shorter the text gets, divide by the separator length and add 1. Wrap that in a user defined function and you get a clean, reusable =Sub_String_Count(text, separator) that works for slashes, semicolons, pipes or any other separator. Download the file, try it on your own lists and keep the formula-only versions in mind for macro-free workbooks.