In some of my reports, I need to find a particular word which is hidden into many comments, it can be in a cell, in a row and/or in a column.

When I use the formula ?

When I need to find a word into huge comments.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=IF()

=SEARCH()

=IFERROR()

=COUNTIF()

=COUNTIFS()

To look into a specific cell:

=IF(SEARCH("*chocolate*",A2,1),"yes")

It will look into the cell A2 and if it finds the word “chocolate” in the comment, it will display “yes”.

To look into a specific column:

=IF(COUNTIF(A:A,"*chocolate*"),"yes","no")

To search with 2 words or more, I will use this formula:

=IF(COUNTIFS(A:A,"*chocolate*",B:B,"*white*"),"yes","no")

If you are using a cell reference instead of the word:

=IF(COUNTIF(A:A,"*"&Z2&"*"),"yes","no")

The cell Z2 is “chocolate”. And ff you get an error like that #VALUE!, use the =IFERROR() function:

=IFERROR(IF(SEARCH("*chocolate*",A2,1),"yes"),"no")