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.

 

[JVID720]

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

M.

Thanks My formula was : =IF(ISERROR(SEARCH(TABLE,SearchTEXT,1)),"NO","YES")

Then CONTROL SHIFT ENTER to make it an array. I had over 200K records and works like a charm

 

[Aladin Akyurek]

Thanks My formula was : =IF(ISERROR(SEARCH(TABLE,SearchTEXT,1)),"NO","YES")

Then CONTROL SHIFT ENTER to make it an array. I had over 200K records and works like a charm

Care to specify what TABLE and SearchText refer to?

 

[nehshere]

Hi,

Is there a way for the formula to return all the keywords found in a string? Even if the string contains more than one keyword?

Thanks,
Neha

 

[Aladin Akyurek]

Hi,

Is there a way for the formula to return all the keywords found in a string? Even if the string contains more than one keyword?

Thanks,
Neha

Row\Col	A​	B​	C​	D​	E​
1​	jad
2​	kad
3​	vad
4​
5​
6​	jad in kad won a vad	jad	kad	vad
7​

A2:A3 is named Keywords.

In B6 control+shift+enter, not just enter, and copy across:

=IFERROR(
    INDEX(Keywords,SMALL(IF(ISNUMBER(SEARCH(" "&Keywords&" "," "&$A6&" ")),
    ROW(Keywords)-ROW(INDEX(Keywords,1,1))+1),COLUMNS($B6:B6))),"")

Hope this helps.

 

[Ingolf]

Hi,

Aladin, can you modify formula to show the answer in one cell (jad, kad, vad) separated by comma.
Thank you.

 

[Aladin Akyurek]

Hi,

Aladin, can you modify formula to show the answer in one cell (jad, kad, vad) separated by comma.
Thank you.

You'll need native TEXTJOIN of the 2016 systems or add the following <acronym title="visual basic for applications">VBA</acronym> code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then invoke...

Control+shift+enter, not just enter:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&Keywords&" "," "&$A6&" ")),", "&Keywords,"")),1,2,"")

 

[Ingolf]

Aladin thanks to replay. I'm interesting only with formula. I use TEXTJOIN and is OK.
Thank you.

 

[Aladin Akyurek]

Aladin thanks to replay. I'm interesting only with formula. I use TEXTJOIN and is OK.
Thank you.

You mean you tried TEXTJOIN and it's ok? (I did not install Excel 2016 version yet, so I can't check.)

 

[Ingolf]

Yes. Thank you.

 

[tamil9288]

Hi,

I have list of text which needs to be searched in the column which contains "Email subject" .

For ex: i want to search a word "STUK","STUS", etc..if this words exist in the subject column, i want to return the same text in the next column.
If STUK is available i want the STUK in the next column.

Please help me with the formula