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]

Create a range housing the relevant search words like STUK, STUS, etc.. sort this range in ascending order, and name the range in question WordList via the Name Box.

Now, in B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&WordList&" "," "&A2&" "),WordList)

where A2 contains an email subject.

 

[imhbk]

Try...

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

Thanks a lot....This is working fine.
is there any way do this same with VBA

I need to apply this in 2 columns (Column A, Column B) with 2 different keywordsTable (1, 2) respectively
column A will work with Keywordtable 1 and column B will work with keywordtable 2
and then return the keyword which found

Thanks for your help!

 

[imhbk]

Thanks a lot....This is working fine.
is there any way do this same with VBA

I need to apply this in 2 columns (Column A, Column B) with 2 different keywordsTable (1, 2) respectively
column A will work with Keywordtable 1 and column B will work with keywordtable 2
and then return the keyword which found

Thanks for your help!

Hi Team,

I really need solution for this. Appreciate your help. Thanks!

 

[Marcelo Branco]

Hi Team,

I really need solution for this. Appreciate your help. Thanks!

This topic is getting too long and you are asking for a solution with VBA. Better create a new Thread by providing an example - data along with before/after macro.

M.

 

[imhbk]

This topic is getting too long and you are asking for a solution with VBA. Better create a new Thread by providing an example - data along with before/after macro.

M.

Thanks Marcelo. I created a new thread for this. looking for help