Search Multiple Text Strings in a Cell

Simonc64

Board Regular
Joined
Feb 15, 2007
Messages
218
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 15, 2007
Messages
218
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 15, 2007
Messages
218

ADVERTISEMENT

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

clabulis

Board Regular
Joined
May 30, 2014
Messages
79
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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

Once that done, invoke:
Rich (BB code):
=IFERROR(IF(COUNT(SEARCH(List,A1))>1,"multiple",
  LOOKUP(9.99999999999999E+307,SEARCH(List,A1),List)),"N/A")
 

clabulis

Board Regular
Joined
May 30, 2014
Messages
79
Create a range housing the relevant key words like emotional, etc. Select the range and name the selection List.

Once that done, invoke:
Rich (BB code):
=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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,706
Messages
5,524,418
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top