Verify Text Exists in a Row of Cells

**cmashburn** · 01-27-2012, 05:03 PM

Hello All,

I could not find any posts that collected all the necessary info to achieve my goal, but if anyone knows of one, please let me know!

Problem:
I need to search an entire worksheet and "highlight", "extract" or in some other way identify which ROWS contain a desired word. It does not matter where the word shows up, what case, or how many times ... I just need to know it is in that ROW of data.

Example:
If the word I am looking for is "Monkey" I need to know what rows of cells contain any version of "Monkey" such as "Monkey", "monkey", or even "bananmonkeylove".

Is this possible? Can anyone help me?

**tigeravatar** · 01-27-2012, 05:19 PM

cmashburn,

Welcome to the forum!
Attached is an example workbook based on the criteria you described. In cell F1 is where you type in what you want to search for. The entire sheet has this conditional format applied to it:
=AND(TRIM($F$1)<>"",COUNTIF(1:1,"*"&$F$1&"*")>0)

**cmashburn** · 01-27-2012, 06:03 PM

tigeravatar,

This is perfect! And so fast! Thank you very much.

Is there a way to alter this to search multiple keywords? If not, no worries. Thank you again!

**tigeravatar** · 01-27-2012, 06:13 PM

You should be able to alter it. Assuming your list of keywords were in F1:F10, you could use this as the conditional format formula:
=SUMPRODUCT(--(TRIM($F$1:$F$10)<>""),--(COUNTIF(1:1,"*"&$F$1:$F$10&"*")>0))>0