Search Multiple Text Strings in a Cell

[Simonc64]

Hi All

I have for example a cell (A1) that contains the following text....."likelihood of mental illness through emotional abuse"

The keyword for me in the text is "emotional"

I have tried the following logic to pull out the keyword =IF(SEARCH("emotional",A1,1)>0,"Emotional") which works fine.

However, the text will contain EITHER one of the following keywords "Emotional", "Physical" or "Mental", OR any combination of the words.

When I try the following formula I just get a #VALUE! error

=IF(SEARCH("emotional",A1,1)>0,"Emotional",IF(SEARCH("physical",A1,1)>0,"Physical"))

Ideally what I want to do is identify the single Keyword that appears in the string OR if a combination of 2 or more of the Keywords appear output the result of "Multiple"

Any thoughts welcomed, thanks !

Simon

 

[Rick Rothstein]

As long as your list of words is not too long, you could do something like this...

=MID(IF(COUNTIF(A1,"*Emotional*"),", Emotional","")&IF(COUNTIF(A1,"*Physical*"),", Physical","")&IF(COUNTIF(A1,"*Mental*"),", Mental",""),3,99)

 

[Simonc64]

Thanks Rick that solves the problem of picking out whichever single keyword appears in the string perfectly. However, I want to avoid bringing all the keywords back if they all appear, I'd just like, for example, the word "Multiple" to appear where more than one of them appear in the same string, would that be possible?

Thanks

Simon

 

[Rick Rothstein]

Thanks Rick that solves the problem of picking out whichever single keyword appears in the string perfectly. However, I want to avoid bringing all the keywords back if they all appear, I'd just like, for example, the word "Multiple" to appear where more than one of them appear in the same string, would that be possible?

This seems to work...

=IF(COUNTIF(A1,"*Emotional*")+COUNTIF(A1,"*Physical*")+COUNTIF(A1,"*Mental*")>1,"Multiple",MID(IF(COUNTIF(A1,"*Emotional*")," Emotional","")&IF(COUNTIF(A1,"*Physical*")," Physical","")&IF(COUNTIF(A1,"*Mental*")," Mental",""),2,99))

 

[Simonc64]

Thanks again Rick that is awesome and solves my problem !!

 

[clabulis]

Here's another option that seems to work nicely:

=IFERROR(IF(COUNT(SEARCH({"Emotional","Mental","Physical"},A1))>1,"Multiple",INDEX({"Emotional","Mental","Physical"},MATCH(1,SEARCH({"Emotional","Mental","Physical"},A1)^0,))),"")

 

[Aladin Akyurek]

Create a range housing the relevant key words like emotional, etc. Select the range and name the selection List.

Once that done, invoke:

=IFERROR(IF(COUNT(SEARCH(List,A1))>1,"multiple",
  LOOKUP(9.99999999999999E+307,SEARCH(List,A1),List)),"N/A")

 

[clabulis]

Create a range housing the relevant key words like emotional, etc. Select the range and name the selection List.

Once that done, invoke:

=IFERROR(IF(COUNT(SEARCH(List,A1))>1,"multiple",
  LOOKUP(9.99999999999999E+307,SEARCH(List,A1),List)),"N/A")

Is the 'IFERROR' necessary if the result is an error anyway?

 

[Rick Rothstein]

Thanks again Rick that is awesome and solves my problem !!

You are welcome, but you should consider Aladin's approach (Message #7) instead as it is more efficient and more flexible (in case you want to add to the list) than my solution.

 

[Aladin Akyurek]

Is the 'IFERROR' necessary if the result is an error anyway?

N/A is not an error, #N/A is, if that's what you mean. If not, try to test the suggestion.