UDF for counting the number of occurrences of text in range

[rnegru]

Hello all,

I don't know if anyone has posted the same question yet, I am sorry but I haven't found it. I use an UDF to count the number of occurrences in any one cell for an inputed string. I have pasted below the code:

Function Count_Word(ByRef sInput As String, ByRef sWord As String) As Long
Dim lCount As Long
Dim lWhere As Long
Dim lWordLen As Long

lWordLen = Len(sWord)

lWhere = InStr(sInput, sWord)

Do While lWhere
lCount = lCount + 1
lWhere = InStr(lWhere + lWordLen, sInput, sWord)
Loop

Count_Word = lCount
End Function


Can anyone help me with a new UDF (or with modifying this one), so that I could count the occurrences of text in a range? I use Excel 2003, if that helps.

Thanks in advance,
Radu

 

[texasalynn]

you don't really need a UDF, try this

Excel Workbook
	A	B
1	Can anyone help me with a new UDF (or with modifying this one), so that I could count the occurrences of text in a range? I use Excel 2003, if that helps.
2	32
Sheet1

 

[MickG]

Hi, Try:-
This will also find the word when its within another word.

Function word(rng [COLOR=navy]As[/COLOR] Range, wd [COLOR=navy]As[/COLOR] String) [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] w [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] c
  [COLOR=navy]For[/COLOR] w = 1 To Len(rng)
    [COLOR=navy]If[/COLOR] Mid(rng, w, Len(wd)) = wd [COLOR=navy]Then[/COLOR]
        c = c + 1
     [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] w
word = c
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[rnegru]

Hi,

Thanks for your reply.

@texasalynn: thanks for the formula, it's a very useful one and I will use it . but i need to be able to search for a certain text in a string, and then count the number of times it occurs. Thanks nonetheless.

@MickG: Your UDF works like a charm when in rng field I select a single cell. However, if I select multiple cells, it evaluates to a #VALUE! error. Any ideas how I could extend the rng to multiple cell selections?

Regards,
Radu

 

[GTO]

Maybe:

Excel Workbook
	A	B
1	Peter Piper picked a peck of pickled peppers; A peck of pickled peppers Peter Piper picked;	4
2	If Peter Piper picked a peck of pickled peppers,	4
3	Where's the peck of pickled peppers Peter Piper picked?	2
Sheet4

Excel 2003

Cell Formulas
Range	Formula
B1	=SUBSTRING_COUNT(A1:A3,"peck")
B2	=SUBSTRING_COUNT(A1:A3,"pickled peppers")
B3	=SUBSTRING_COUNT(A1,"pickled peppers")

In a Standard Module:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> SubString_Count(CellAddress<SPAN style="color:#00007F">As</SPAN> Range, _<br>                         TestSubString<SPAN style="color:#00007F">As</SPAN> String, _<br>                        <SPAN style="color:#00007F">Optional</SPAN> bolIgnoreCase<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN> =<SPAN style="color:#00007F">False</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>Dim _<br>oMatches    <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, _<br>ary        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>val        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, _<br>lCount      <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>    <br>    lCount = 0<br>    <br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>        .Global =<SPAN style="color:#00007F">True</SPAN><br>        .IgnoreCase = bolIgnoreCase<br>        .Pattern = "\b" & TestSubString & "\b"<br>        <br>        <SPAN style="color:#00007F">If</SPAN> CellAddress.Cells.Count > 1<SPAN style="color:#00007F">Then</SPAN><br>        <br>            ary = CellAddress.Value<br>            <br>            <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> val<SPAN style="color:#00007F">In</SPAN> ary<br>                <SPAN style="color:#00007F">If</SPAN> .Test(val)<SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">Set</SPAN> oMatches = .Execute(val)<br>                    lCount = lCount + oMatches.Count<br>                <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> .Test(CellAddress.Value)<SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> oMatches = .Execute(CellAddress.Value)<br>                lCount = oMatches.Count<br>            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><br>    <br>    SubString_Count = lCount<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Hope that helps,

Mark

 

[rnegru]

@GTO: Hi, Mark. Worked like a charm. Thanks a million

Regards,
Radu

 

[GTO]

Hi Radu, Happy to help