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

Question

I have a column with some string description in it.

for example:

Bob davids
mowing the lawn
tipping cows

In addition I will have on a different sheet or column a list of keywords For example work keyword list 1:

davids
work

play keyword list:

mowing
cows

So as the main column is populated with these text strings, I would like them checked automatically each keyword list to see if those words exist, and when it finds a match, place the title of the list (work/play) in the cell next to it.

I know this is possible in VBA and can even do it "on the fly" in SelectionChange function. But is this possible to do without VBA such as a conditional formatting?

Solution 1

This is pretty easy to do with just formulas, as long as you don't care too much about possibly improperly finding parts of words. Ignore that caveat for a second though. First, here is a formula that will tell you if any of several strings are found anywhere within a source string:

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

This needs to be entered as an array formula for it to work. You do that by entering it with Ctrl-Shift-Enter. To understand how it works, consider what Excel does in evaluating a real example:

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

'FIND' finds the position of one string within another. When called with an array for the first argument, it will return an array of positions (or #VALUE! if the search string isn't found). You can trace the evaluation by entering that formula and then using the F9 key on expressions within it:

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

So, for your example, say you had your strings you want searched in $B$6:$B$8, your work strings in $D$2:$D$3, and your play strings in $E$2:$E$3. You could 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 through the range D6:E8 to find which strings in B had work or play words in them. Then you could use those results to drive further formulas or conditional formatting.

However, as mentioned above, you'll note that any substring within the string being searched will get found, so

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

will evaluate to TRUE. (And if your fun list includes "id", the "id" in "davids" will match.)

As is often the case with Excel, if you're doing something you understand with a limited data set, you might not care about this. But it can defeat an attempt to use this kind of formula as part of a general "application" that has users who don't know fancy array tricks or exactly what 'FIND' does. (You can sort of get around that by putting a space after your search words, etc., but that is just more mysterious voodoo waiting to be broken if you hand it someone else.) For a quick and dirty scan, though, it's fine.

Solution 2

I've put together another way of doing this for a small number. As was intimated, this gets cumbersome quickly.

Input the following into column A: 1: horse cow pig chicken 2: dog cat bird 3: fish cat 4: horse cat mouse 5: ape human dolphin 6: dog cat chipmunk

In Cell B1 enter the following (and copy into 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 appear blank whereas the result in B2, B3, B4, and B5 should read "House pet".

Careful if the list of keywords gets too big. I used this successfully for short lists too (as implied in the question) but you have limited space to type all the possibilities if its too long - not to mention risk errors in the formula.

I couldn't get my arrays to work - thanks for the post above I'm going to go give this a try now too for my longer lists.

ps. Don't remember what I was doing with my OR ISERROR array but this array you've provided worked beautifully for my list of 80 keywords. Thanks!