Smart Text Searching

[McStagger]

Using a function like COUNTIF, is there a good way to look for specific text in a cell that will not include words with prefixes? Example, searching for "like" returning counts that include "dislike".

To complicate this, search cells will have sentences of text so it is not as simple as just searching for a quoted word without wildcards.

[shg]

	A	B	C
1	I don't like him	3	B1: =SUMPRODUCT(--ISNUMBER(SEARCH(" like", " " & A1:A4)))
2	I don't dislike him
3	Like you'd know
4	Likely true

[McStagger]

Thank you SHG! That is indeed working.

My only issue now is I do not quite understand why. I am not getting how the formula is differentiating between "like" and "dislike" while still picking up "likely" correctly.

Would you or someone else expound on that some, please?

[shg]

It looks for a space before the word "like"

[McStagger]

I get that. What I am missing is how it is picking up "Like" when it is at the front of a sentence which has no space preceding "like".

Using COUNTIF, I tried something like that in my original formula and it was not finding "like" when it was front of a sentence.

[shg]

how it is picking up "Like" when it is at the front of a sentence

=SUMPRODUCT(--ISNUMBER(SEARCH(" like", " " & A1:A4)))