How to search a cell for multiple keywords?
 

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!

How to search a cell for multiple keywords?
 

one way:

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

In article . com,
mr_espresso wrote:

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!

How to search a cell for multiple keywords?
 

Brilliant.

Thanks!

On Apr 27, 6:40 pm, JE McGimpsey wrote:
one way:

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

In article . com,

mr_espresso wrote:
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!

How to search a cell for multiple keywords?
 

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" wrote:

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!

How to search a cell for multiple keywords?
 

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.

On Apr 28, 10:56 am, JMB wrote:
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" wrote:
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!

How to search a cell for multiple keywords?
 

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" wrote:

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.

On Apr 28, 10:56 am, JMB wrote:
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" wrote:
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!

How to search a cell for multiple keywords?
 

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!

On Apr 30, 7:19 pm, JMB wrote:
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" wrote:
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.

On Apr 28, 10:56 am, JMB wrote:
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" wrote:
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!

How to search a cell for multiple keywords?
 

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.

On May 1, 9:20 am, mr_espresso wrote:
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!

On Apr 30, 7:19 pm, JMB wrote:

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" wrote:
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.

On Apr 28, 10:56 am, JMB wrote:
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" wrote:
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!

How to search a cell for multiple keywords?
 

Some information here regarding calculation speed.

http://www.decisionmodels.com/


"mr_espresso" wrote:

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.

On May 1, 9:20 am, mr_espresso wrote:
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!

On Apr 30, 7:19 pm, JMB wrote:

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" wrote:
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.

On Apr 28, 10:56 am, JMB wrote:
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" wrote:
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!