How to search a cell for multiple keywords?

[mr_espresso]

Since the search function only allows one search term at a time, how
can one search a cell for several keywords?

For example, I want to search a survey comments field for terms like
<doctor, nurse, paramedic> and I need to use wildcards like <doct*>.
Ideally the function would return a 1 if one or more of the keywords
is present, and a 0 if none are found.

My apologies if this question already has been addressed, but I didn't
see anything in the archives.

Thank you in advance!

 

[JE McGimpsey]

one way:

=--(SUM(COUNTIF(A1:A100,{"*doc*","*nurs*","*paramed*"}))>0)

 

[mr_espresso]

Brilliant.

Thanks!

one way:

=--(SUM(COUNTIF(A1:A100,{"*doc*","*nurs*","*paramed*"}))>0)

 

[Guest]

Actually, you could use search for multiple terms. Assuming your data is in
A1:A9

=--(SUMPRODUCT(--(ISNUMBER(SEARCH({"doc*","nur*","param*"},A1:A9))))>0)

But, it has to use an extra nested function call (Isnumber), so it's not as
efficient as JE's.

 

[mr_espresso]

Thanks, JMB.

Your formula may be less efficient, but it actually worked better. The
other formula mistakenly assigned 0's to cells which should have been
1's.

 

[Guest]

Maybe the difference is JE has a wildcard at the beginning of the criteria???

*doc*
*nur*
*paramed*

Also, both JE's and mine evaluate a range of cells, so if any of those
criteria are present in any cell in that range - it will return a 1. If you
want a separate indicator for each cell in the range being searched, adjust
the range reference.

Using my example:

=--(SUMPRODUCT(--(ISNUMBER(SEARCH({"doc*","nur*","param*"},A1))))>0)

entered in B1 then drag down to B9.

 

[mr_espresso]

I'm using a wildcard before and after in your formula too, so the only
difference is the formula itself.

The only downside of your formula is that it's VERY slow to calculate
(and I have a fast computer). There is one target cell being analyzed
and it's about 100 words long, but I'm working with 1000 cases / rows,
so there are approximately 100,000 words to analyze each time I hit
<calculate>.

Plus, I'm searching for about 10 different groups of keywords (e.g. 1:
doctor / nurse / paramedic , 2: university / college, 3: goal /
aspiration / dream ...)

Any tips on speeding up the calculations? Every time I change a
formula and want to recalculate, Excel takes forever. I tried
selecting just the column I want to recalculate but Excel keeps going
to the next column, etc.

Thanks again!

 

[mr_espresso]

To answer my own question: putting each search in a different
worksheet speeds things up considerably. It still takes about 1 minute
to recalculate but this is way faster than before.

 

[Guest]

Some information here regarding calculation speed.

http://www.decisionmodels.com/