Search for multiple text strings simultaneously in a single cell

[geekylawyer]

I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.

If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.

What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".

So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.

So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.

To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks). That's all.

 

[Aladin Akyurek]

Hi, the below formula is really helpful.
Question - is there a way to bring the word that was found, instead of "Yes" / "No"?
thanks a lot!

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)

 

[laviz]

WOWWWWW excellent, thanks a lot!

 

[Aladin Akyurek]

WOWWWWW excellent, thanks a lot!

You are welcome.

 

[mickarose]

An example

	A​	B​	C​	D​
1​	Text​	Keyword​		Keywords​
2​	text key3 text​	Key3​		Key1​
3​	key4 text​	Key4​		Key2​
4​	text key1​	Key1​		Key3​
5​	text text​	Not Found​		Key4​
6​	text key2 key4​	Key2​		Key5​

D2:D6 is a named range --> Keywords

Array formula in B2 copied down

=IFERROR(INDEX(Keywords,MATCH(TRUE,ISNUMBER(SEARCH(" "&Keywords&" "," "&A2&" ")),0)),"Not Found")

Confirmed with Crtl+Shift+Enter

Remark: If the cell contains more than one keyword (like A6) the formula returns just the first keyword found.

Hope this helps

M.

This is almost exactly as I need too, so thank you very much for the reply.

If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)

 

[Aladin Akyurek]

This is almost exactly as I need too, so thank you very much for the reply.

If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)

That can be done either each return in a cell of its own or comma-separated in a single cell using ACONCAT, a function in VBA.

 

[aquariaj1]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)

I joined the forum just to say THANK YOU!!

 

[Aladin Akyurek]

I joined the forum just to say THANK YOU!!

You are welcome.

 

[macci]

An example

	A​	B​	C​	D​
1​	Text​	Keyword​		Keywords​
2​	text key3 text​	Key3​		Key1​
3​	key4 text​	Key4​		Key2​
4​	text key1​	Key1​		Key3​
5​	text text​	Not Found​		Key4​
6​	text key2 key4​	Key2​		Key5​

<tbody>
</tbody>

D2:D6 is a named range --> Keywords

Array formula in B2 copied down

=IFERROR(INDEX(Keywords,MATCH(TRUE,ISNUMBER(SEARCH(" "&Keywords&" "," "&A2&" ")),0)),"Not Found")

Confirmed with Crtl+Shift+Enter

Remark: If the cell contains more than one keyword (like A6) the formula returns just the first keyword found.

Hope this helps

M.

Thank you! Works like a charm!

One more question:

How can it be done with another column: E(Answers) ?

E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B

	A​	B​	C​	D​	E​
1​	Text​	Answer ​		Keywords​	Answers​
2​	text key3 text​	Answer3 ​		Key1​	Answer1 ​
3​	key4 text​	Answer4 ​		Key2​	Answer2​
4​	text key1​	Answer1 ​		Key3​	Answer3​
5​	text text​	Not Found ​		Key4​	Answer4​
6​	text key2 key4​	Answer2 ​		Key5​	Answer5 ​

<tbody>
</tbody>

 

[Aladin Akyurek]

Thank you! Works like a charm!

One more question:

How can it be done with another column: E(Answers) ?

E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B

	A​	B​	C​	D​	E​
1​	Text​	Answer ​		Keywords​	Answers​
2​	text key3 text​	Answer3 ​		Key1​	Answer1 ​
3​	key4 text​	Answer4 ​		Key2​	Answer2​
4​	text key1​	Answer1 ​		Key3​	Answer3​
5​	text text​	Not Found ​		Key4​	Answer4​
6​	text key2 key4​	Answer2 ​		Key5​	Answer5 ​

<tbody>
</tbody>

In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH(" "&$D$2:$D$6&" "," "&A2&" ")),0)),"Not Found")

 

[macci]

ray: