Counting a specfic substring in a cell without counting another string that contains the same substring in that cell

[dagda13]

Hi,

I'm trying to do a count of specific substring in a range of cells. I've been able to count for substrings in a cell using the following formula:

=COUNTIF(A1:A3,"*fox*")

However, I'm running into trouble when the substring I'm counting for exists in the same cell in another string:
A
1 foxes
2 foxes, fox
3 fox, foxes

I'd like to get a hit only if the cell contains the substring "fox" on it's own, and not a string that contains the substring, ie., "foxes". For the above, the count should be 2, but I'm getting 5. Any idea how to go about this?

Thanks!

 

[Marcelo Branco]

Try

Book1
	A	B	C	D
1	Text		Criteria	Result
2	foxes		fox	2
3	foxes, fox
4	fox, foxes
Plan1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--ISNUMBER(SEARCH(","&C2&",",","&SUBSTITUTE(A2:A4," ","")&",")))

Hope this helps

M.

 

[yky]

I got 3. Why did you get 5? My Excel counts "fox,foxes" as 1, not 2.

If you get 5, then use this formula:

=COUNTIF(A1:A3,"*fox*") - COUNTIF(A1:A3,"*foxe*")

Since I got 3, this formula gives 0 but if you get 5, then, 5-3 = 2, which is what you want.

 

[dagda13]

Try

Book1
	A	B	C	D
1	Text		Criteria	Result
2	foxes		fox	2
3	foxes, fox
4	fox, foxes
Plan1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--ISNUMBER(SEARCH(","&C2&",",","&SUBSTITUTE(A2:A4," ","")&",")))

Hope this helps

M.

Thanks so much, this works perfectly!

 

[dagda13]

I got 3. Why did you get 5? My Excel counts "fox,foxes" as 1, not 2.

If you get 5, then use this formula:

=COUNTIF(A1:A3,"*fox*") - COUNTIF(A1:A3,"*foxe*")

Since I got 3, this formula gives 0 but if you get 5, then, 5-3 = 2, which is what you want.

Thanks very mucn, yky! This works too.