Thread: Finding the frequency of a string in other string(s)

Received from an email:

Originally Posted by vkcham van

I'm looking for a formula that will count specific word in a cell.

Let say cell a1 has "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW"
I want to know how many WWLL are in this cell, which is 4.

Thank you - sam

I wrote this custom function to provide the answer. It is used like so:

=COUNTSTRING(A1, "WWLL")
=COUNTSTRING(A1:A10, "WWLL")
=COUNTSTRING(A1, B1) (B1 holds the text string WWLL)

The first parameter is a cell or range of cells. The second parameter is the string to search for as a single cell reference or text string.

Code:

Function COUNTSTRING(RNG As Range, MyStr As String) As Long
'JBeaucaire  (12/8/2009)
'Count the frequency of a string in other strings
Dim cell As Range, MyCnt As Long, i As Long
If MyStr = "" Then GoTo ErrorExit

    For Each cell In RNG
        For i = 1 To (Len(cell) - Len(MyStr) + 1)
            If Mid(cell, i, Len(MyStr)) = MyStr Then MyCnt = MyCnt + 1
        Next i
    Next cell

COUNTSTRING = MyCnt
Exit Function

ErrorExit:
    COUNTSTRING = 0
End Function

==========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use in cell as shown above.

hi JB

excelent

we need a "Free code Section"

Moved to Tips & Tutorials.

Worth adding perhaps that the same could be achieved natively with:

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

where A1 is original string and B1 string of interest

And for range

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,""))))/LEN(B1)

(the latter could be applied via a single Evaluate call in UDF terms based on address of range specified in the args)

I had to move the parens to get Don's to work, but very slick indeed! Better than a UDF! Which is why I posted, wondered what the gang would come up with...

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

thanks JB - yes I missed the closing parenthesis (never code on the fly!)

I tried to get Don's formula to work inside the UDF and no dice. Anybody spot the error?

Code:

Function COUNTSTRING2(RNG As Range, MyStr As String) As Long
'Count the frequency of a string in other strings

    COUNTSTRING2 = Evaluate("=SUMPRODUCT((LEN(" & RNG & ")-LEN(SUBSTITUTE(" & RNG & "," & MyStr & ",""""))))/LEN(" & MyStr & ")")

End Function

You need to encase your string variable within quotations and also when using Evaluate you should use .Address

One other point, when using Evaluate widely I'd say it's generally best to qualify the object against which you wish for it to be applied.

Code:

COUNTSTRING2 = RNG.Parent.Evaluate("SUMPRODUCT((LEN(" & RNG.Address & ")-LEN(SUBSTITUTE(" & RNG.Address & ",""" & MyStr & """,""""))))/LEN(""" & MyStr & """)")

why do you have that closing parens at the end? does MyStr open it?

MyStr & ")")

It is closing the LEN function

Bigdaddy, please post questions in one of the question forums. This is not one. You can link to this thread, to provide the context.

Please make sure that when you quote, you use quote tags like this [quote] the quoted text[/quote], so people can actually identify where your contribution starts.

cheers

Teylyn, Bigdaddy is an alias for the guy who sent the original email.

just a thought

Code:

Function jtest(Rng$, Mystr$) As Double
    Dim RegEx As Object, Matches As Object
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True: .IgnoreCase = True: .Pattern = "(" & Rng & ")"
        Set Matches = .Execute(Mystr)
               jtest = Matches.Count
       End With
End Function