Report if Text String Contains a Specific Word

[CJoshuav]

I have a column of cells with narratives in them. I need to flag the ones that contain specific words.

Currently, I am using

=IF(COUNT(SEARCH("dog",M2)),"Y","")

This works fine for catching everything with "dog," but it also "dogged" and "doggerel."

How do I tweak the formula so that it only catches "dog" and not words that contain that letter combination.

Thanks!

 

[ranman256]

did you try it with a space on each end of DOG
" dog "

 

[CJoshuav]

I did, and it ignored all of them.

 

[Jonmo1]

You would have to add the spaces to the searched cell as well.
i.e. If the cell was "dog ate my homework"
It wouldn't find " dog " because there is no space in front of dog in the cell.

Try
=IF(COUNT(SEARCH(" dog "," "&M2&" ")),"Y","")

 

[CJoshuav]

Try
=IF(COUNT(SEARCH(" dog "," "&M2&" ")),"Y","")

That did it! Thanks so much!

 

[Rick Rothstein]

You would have to add the spaces to the searched cell as well.
i.e. If the cell was "dog ate my homework"
It wouldn't find " dog " because there is no space in front of dog in the cell.

Try
=IF(COUNT(SEARCH(" dog "," "&M2&" ")),"Y","")

Of course, this won't work if the word "dog" is at the beginning or end of a quoted or bracketed phrase or if it is next to a comma, period, question mark, exclamation mark, dash and so on. I would also note that, as written, your formula would consider 123dog456 to be the standalone word "dog". Here is a formula that should work correctly for all these possibilities...

=IF(ISNUMBER(SEARCH("dog", A2)), AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH("dog", A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH("dog", A2)+LEN("dog")+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")))))

Of course, the word "dog" can be placed in a cell and then the four instances of "dog" can be replaced with a reference to that cell (using either absolute or relative addressing depending on whether the cell reference should change as the formula is dragged down).

This can also be written as a UFD (user defined function) if desired...

[SIZE=1]Function IsWordInString(Text As String, Word As String) As Boolean
  IsWordInString = " " & UCase(Text) & " " Like "*[!A-Z0-9]" & UCase(Word) & "[!A-Z0-9]*"
End Function[/SIZE]

 

[Jonmo1]

You're welcome.

 

[Rick Rothstein]

Of course, this won't work if the word "dog" is at the beginning or end of a quoted or bracketed phrase or if it is next to a comma, period, question mark, exclamation mark, dash and so on. I would also note that, as written, your formula would consider 123dog456 to be the standalone word "dog". Here is a formula that should work correctly for all these possibilities...

=IF(ISNUMBER(SEARCH("dog", A2)), AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH("dog", A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH("dog", A2)+LEN("dog")+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")))))

Of course, the word "dog" can be placed in a cell and then the four instances of "dog" can be replaced with a reference to that cell (using either absolute or relative addressing depending on whether the cell reference should change as the formula is dragged down).

This can also be written as a UFD (user defined function) if desired...

[SIZE=1]Function IsWordInString(Text As String, Word As String) As Boolean
  IsWordInString = " " & UCase(Text) & " " Like "*[!A-Z0-9]" & UCase(Word) & "[!A-Z0-9]*"
End Function[/SIZE]

Of course, the OP wanted "Y" and "" returned, not TRUE and FALSE, so here are the above formula and UDF modified to return those values...

=IF(ISNUMBER(SEARCH("dog", A1)), IF(AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A1&"=", SEARCH("dog", A1), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A1&"=", SEARCH("dog", A1)+LEN("dog")+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")))),"Y",""),"")

[SIZE=1]Function IsWordInString(Text As String, Word As String) As String
  If " " & UCase(Text) & " " Like "*[!A-Z0-9]" & UCase(Word) & "[!A-Z0-9]*" Then IsWordInString = "Y"
End Function[/SIZE]

 

[CJoshuav]

Rick - That's genius. Thanks so much!