Search for multiple text strings simultaneously in a single cell

geekylawyer

New Member
Joined
May 23, 2013
Messages
3
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi and welcome to Mr Excel

Maybe...

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Yes","No")

where KeywordTable is a named range containing all keywords

M.
 
Upvote 0
That's closer...right now when I click "evaluate formula" though, its giving me this

=if(sumproduct(isnumber({#value!;#value!;26;#value!;Value!}),"Yes",No")
=if(sumproduct(isnumber({false;false;true;false;false}),"Yes","No")
=if(0,"Yes","No")
=No

So, clearly, one of the keywords is present (as evidenced by the "26" and the "true" in the middle of the string). I'm trying to get it to say "if there is even one true, it is all true", but instead, because of the #value! errors for the keywords that aren't present, it's defaulting to a "0"/"false" and a "no". Even though one of the keywords is, in fact, present.
 
Upvote 0
I got it!! It took a lot of browsing around other forums. But this works:

=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")

I just had to hit CTRL+SHIFT+ENTER to turn it into an array function at the end.

Yay!
 
Upvote 0
I added a helper column, if you can do that try out this

Excel 2010
JKLMNO
6datalook upColumn1
7thehowyes
8andwhoyes
9yestheyjanno
10themjaneno
11isdoeno
12andheno
13whyhimno
14howtheno
15whowhoyes
Sheet1
Cell Formulas
RangeFormula
O7=IF(ISNUMBER(MATCH(N7,L7:L15,0)),"yes","no")
O8=IF(ISNUMBER(MATCH(N8,L8:L16,0)),"yes","no")
O9=IF(ISNUMBER(MATCH(N9,L9:L17,0)),"yes","no")
O10=IF(ISNUMBER(MATCH(N10,L10:L18,0)),"yes","no")
O11=IF(ISNUMBER(MATCH(N11,L11:L19,0)),"yes","no")
O12=IF(ISNUMBER(MATCH(N12,L12:L20,0)),"yes","no")
O13=IF(ISNUMBER(MATCH(N13,L13:L21,0)),"yes","no")
O14=IF(ISNUMBER(MATCH(N14,L14:L22,0)),"yes","no")
O15=IF(ISNUMBER(MATCH(N15,L15:L23,0)),"yes","no")
J9{=IF(O7:O15="yes","yes","No")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
That's closer...right now when I click "evaluate formula" though, its giving me this

=if(sumproduct(isnumber({#value!;#value!;26;#value!;Value!}),"Yes",No")
=if(sumproduct(isnumber({false;false;true;false;false}),"Yes","No")
=if(0,"Yes","No")
=No

So, clearly, one of the keywords is present (as evidenced by the "26" and the "true" in the middle of the string). I'm trying to get it to say "if there is even one true, it is all true", but instead, because of the #value! errors for the keywords that aren't present, it's defaulting to a "0"/"false" and a "no". Even though one of the keywords is, in fact, present.

You missed the -- before ISNUMBER.
-- coerces (converts) logical values TRUE or FALSE to, respectively, 1 or 0.

M.
 
Upvote 0
I got it!! It took a lot of browsing around other forums. But this works:

=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")

I just had to hit CTRL+SHIFT+ENTER to turn it into an array function at the end.

Yay!

Good job! Your formula is correct, but it requires Ctrl+Shift+Enter

M.
 
Upvote 0
For this formula to work for an entire range of cells, you have to first highlight the range you want to formula's to be in, then enter the Array formula, then hit CTRL+SHIFT+ENTER Correct?
 
Upvote 0
Hi and welcome to Mr Excel

Maybe...

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Yes","No")

where KeywordTable is a named range containing all keywords

M.

Can we build on this? Having checked that the KEYWORD appears in a cell (as above) instead of "YES" it would be useful to have the actual KEYWORD. So IF "YES" THEN relevant keyword.
 
Upvote 0
Can we build on this? Having checked that the KEYWORD appears in a cell (as above) instead of "YES" it would be useful to have the actual KEYWORD. So IF "YES" THEN relevant keyword.

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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top