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.
Bookmarks