If a cell contains 1 or more keywords, change the value of another cell

Question

I have a column with some string description.

eg:

  Bob davids
 mowing the lawn
 tipping cows 

In addition, I will have a list of keywords on another sheet or column. For example, keyword list 1:

  davids
 work 

keyword list:

  mowing
 cows 

Since the main column is filled with these text lines, I would like them to automatically check each keyword list to see if these words exist, and when it finds a match, put the list heading (work / play) in the cell next to it.

I know that this is possible in VBA and can even do it on the fly in the SelectionChange function. But is it possible to do without VBA, for example conditional formatting?

Answer 1

This is fairly easy to do with just formulas if you don’t care too much about possibly finding parts of words incorrectly. However, ignore this disclaimer. Firstly, here is a formula that will tell you if any of several lines are found anywhere in the source line:

 =OR(NOT(ISERROR(FIND(<array of strings to look for>,<string to look in>)))) 

This must be entered as an array formula for its operation. You do this by typing it with Ctrl-Shift-Enter. To understand how this works, consider what Excel does when evaluating a real-world example:

 =OR(NOT(ISERROR(FIND({"a","b","c"},"qbq")))) 

"FIND" finds the position of one line in another. When called with an array for the first argument, it returns an array of positions (or #VALUE! If the search string is not found). You can trace the score by entering this formula, and then using the F9 key in the expressions inside it:

 =OR(NOT(ISERROR({#VALUE!,3,#VALUE!}))) =OR(NOT({TRUE,FALSE,TRUE})) =OR({FALSE,TRUE,FALSE}) =TRUE 

So, for your example, let's say you have your lines that you want to find in $ B $ 6: $ B $ 8, your working lines in $ D $ 2: $ D $ 3, and your game lines in $ E $ 2: $ E $ 3, you can put the formula

 =OR(NOT(ISERROR(FIND(D$2:D$3,$B6)))) 

in cell D6, enter it as an array formula, and then drag it into the D6: E8 range to find which lines in B worked or played words in them. You can then use these results to control other formulas or conditional formatting.

However, as mentioned above, you will notice that any substring inside the search string will be found, therefore

 =OR(NOT(ISERROR(FIND({"a","b","c"},"bad")))) 

will be rated as TRUE. (And if your list of funny words includes "id", the "id" in "davids" will match.)

As is often the case with Excel, if you do something that you understand with a limited set of data, you may not care. But he can defeat the attempt to use such a formula as part of a general “application” in which there are users who do not know fancy tricks with arrays or exactly what “FIND” does. (You can get around this by placing a space after the search words, etc., but it's just a more mysterious voodoo, expecting it to break if you pass it on to someone else.) However, for a quick and dirty scan, this perfectly.

Answer 2

I put together another way to do this for a small number. As scheduled, this quickly becomes cumbersome.

Enter the following in column A: 1: cow pig chick 2: dog cat bird 3: fish cat 4: horse mouse 5: human dolphin 6: chipmunk with a dog

In cell B1, enter the following (and copy to B2: B6):

 =if(sum(iserror(find("cat",A1)),iserror(find("dog",A1)),iserror(find("fish",A1)),iserror(find("bird",A1)))<4,"House pet","") 

The result in B1 and B5 should look empty, while the result in B2 , B3 , B4 and B5 should read "Pet."

Caution if the keyword list gets too big. I also used this for short lists (as implied in the question), but you have limited space to gain all the possibilities if it is too long - not to mention the risk errors in the formula.

I could not get my arrays to work - thanks for the post above. I'm going to go try now too for my longer listings.

ps. Don't remember what I did with the OR ISERROR , but this array you provided worked fine for my list of 80 keywords. Thanks!