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.

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.