Excel count substring in string or text

This Excel tutorial explains how to count substring in string (text).

Excel count substring in string (text)

Excel does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a Macro solution for your reference.

Excel count substring in string using Len and Substitute Function

Excel Len Function is to measure the length of text.

Excel Substitute Function is to replace a substring in a text.

To count substring in string, simply replace the target substring with nothing (removing the substring), and then measure the difference of text length before and after substitute, and then divide the difference by length of substring.

For example, A1 contains the string, while B1 is the substring.

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)

Excel VBA custom Function to count substring in string

I created this custom Function long time ago before I knew the Len and Substitute trick, now this is for your reference.

Public Function wCountSubStr(str As String, substr As String) As Integer
    lenstr = Len(str)
    lensubstr = Len(substr)
    
    For i = 1 To lenstr
        tempString = Mid(str, i, lensubstr)
        If tempString = substr Then
            count = count + 1
        End If
    Next i
    wCountSubStr = count
End Function

Syntax of Custom Function – count substring in string

wCountSubStr(str, substr)

str String to be evaluated
substr Substring that you want to count

Example of Custom Function – count substring in string

Formula Result
=wCountSubStr(“123412″,”12”) 2
=wCountSubStr(“#**$”,”1″) 0

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

One thought on “Excel count substring in string or text

  1. The formula with the substitute does not give the right answer if one would like to count the number of a substring in another string when that string contains more then 1 subsequent combinations of the searchstring.
    Try this formula with the substring “qq” and the string to be investigated contains “qqq”. The answer given is 1 but the right answer should be 2: the cobination of the first 2 q’s plus the combination of the second and last q.
    Your function gives the right answer!
    I do have a question:
    How would you use the function if I want to investigate the entire column A with the substring in B1?

Leave a Reply

Your email address will not be published. Required fields are marked *