Countif(substring)

[MartinL]

Can I count the number of time a substring appears in a list

ie:

Excel Workbook
	A	B
1		String to find
2		is a
3	This is a string	3
4	This is also a string
5	This is a mistake
6	This is a sentence
Sheet1

 

[VoG]

Try

=COUNTIF(A3:A6,"*"&B2&" *")

 

[MartinL]

Hi Peter

I tried it on my licve work and it didnt work
then I tried it on another string and it did work
then i noticed, that the string i was searching for was the only thing in the cell and it appeared only once anyway.

I think this solution does not work if the substring is at the beginning (and possibly end) of the string

Ugghhhhh!

Martin

 

[GTO]

Hi All,

As Peter knows, I'm not much at formulas, but I think there would be an issue with stuff like "is also" with the wilcard.

Not well tested, but I was thinking maybe testing against the word boundary on both ends might work.

Excel Workbook
	A	B
1		String to find
2		is a
3	This is a string	4
4	This is also a string
5	This is a mistake
6	This is a sentence
7	No, that is a mistake.
Sheet1

Excel 2000

Cell Formulas
Range	Formula
B3	=SubStrings_Count(A3:A7,B2,TRUE,TRUE)

With the UDF 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> SubStrings_Count(CellRange<SPAN style="color:#00007F">As</SPAN> Range, _<br>                          SubString<SPAN style="color:#00007F">As</SPAN> String, _<br>                          <SPAN style="color:#00007F">Optional</SPAN> AllTimesPerCell<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN> =<SPAN style="color:#00007F">True</SPAN>, _<br>                          <SPAN style="color:#00007F">Optional</SPAN> bolIgnoreCase<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN> = True _<br>                          )<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>                          <br><SPAN style="color:#00007F">Dim</SPAN> REX<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><SPAN style="color:#007F00">'< RegExp</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rexMatches<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><SPAN style="color:#007F00">'< MatchCollection</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rCell<SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> lCount<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>    lCount = 0<br>    <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Global = AllTimesPerCell<br>        .IgnoreCase = bolIgnoreCase<br>        .Pattern = "\b" & SubString & "\b"<br>        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> CellRange<br>            <SPAN style="color:#00007F">If</SPAN> .Test(rCell)<SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> rexMatches = .Execute(rCell)<br>                lCount = lCount + rexMatches.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">End</SPAN><SPAN style="color:#00007F">With</SPAN><br>    SubStrings_Count = lCount<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Hope that helps,

Mark

 

[MartinL]

Hi Mark

That is really sweet, and it appears to work.
The use of parameters (all times per cell and case sensitivity are beautiful)
Using a named range is now a delight.

thanks so much.

Just wish someone would comment on my other post )

http://www.mrexcel.com/forum/showthread.php?p=2142583#post2142583
just in case you were wondering

Martin