Check if string contains keyword from a list, regardless of case or positioning in string

Hello,

I'm trying to figure out a formula that will check if a string contains any words from a separate array of strings (aka if a cell contains one or more keywords from a list.) The formula should return TRUE any of the keywords appears in the string regardless of case or position of the keyword in the string, and FALSE otherwise.

1. For a match I don't want to have to consider case (i.e. if the keyword is "CAT" and the string contains "cat" I would like to return TRUE.)
2. It should be considered a match if the keyword is contained anywhere in the string we are checking (i.e. if the keyword is "Cat" and the string is "Cat/hat" or "bobcat" I would like to return TRUE)


I have checked around the forums and have only found cases that don't account for the lack of case or carrying of positing.

I really appreciate any help on this!

Thanks!!

Try this:

If your data looks like:

PHP Code: 

          A                   
1) Target_List                
2) Dogs, Cats, Rabbits
3) Catapiller
4) Ducks, Geese
5) Turkey, Dove
6) Cat'o Nine Tails
7) Catering 


Put your Search Word in Cell C1

Put this formula in B2 and drag down:

=NOT(ISERROR(FIND(LOWER($C$1),LOWER($A2),1)))

You should get a TRUE, FALSE list.

For a different way of doing this, use the same
formula, but use Conditional Formatting:

Conditional Formatting 2007

1) Select your cell or range of cells, IE A2:A7
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=NOT(ISERROR(FIND(LOWER($C$1),LOWER($A2),1)))

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Now all the strings that have your Search Word, should change color.

xenixman,

Thank you very much for your thoughts! This is great way to account for the difference in case!

Unfortunately the one thing that your solution doesn't cover is that I am trying to do this for an *array* of search words, not just one. Your example works perfectly for one search word, but if I put a search word in C1 and another in C2 and modify your formula to:

=NOT(ISERROR(FIND(LOWER($C$1:$C$2),LOWER($A2),1)))

Then it no longer works. I thought that entering this as an Array formula (by entering the formula with Cntrl+Shift+Enter) might solve the problem, but alas.

Tigeravatar,

That formula worked perfectly! Thanks for your welcome and your help!!

Got it. Thanks again!

Hi

thanks for this it is a great formula. To add a little extra, is there anyway of not only identifying if the text from a list is present, BUT also which word is present? ie is it "cat" or "fish" as per the model provided?

Thanks.