Check for Multiple Text Strings in a Cell

[David44357]

This formula will search the B cell for a word and post an indicator if it contains the keyword.
=IF(ISNUMBER(SEARCH("citations",B170)),"1", "")

My obstacle is I need four of these that each search for 3-4 different keywords.

I've tried things like:
=IF(ISNUMBER(OR(SEARCH("citations",B170)), SEARCH("sources",B170))),"1", "")

But I get errors. Does anyone know how to get a formula that checks a cell for multiple keywords? I keep trying to put the OR in different places without success.

-David

 

[Special-K99]

This will check for "a" or "b" in A1 and return TRUE if either is found.
Just add more NOT(ISERR(SEARCH...)) formulas within the OR formula

=OR(NOT(ISERR(SEARCH("a",A1))),NOT(ISERR(SEARCH("b",A1))))

 

[Marcelo Branco]

Maybe

Say your words of interest are in
A1=citations
A2=source
A3=sometext
A4=anothertext

Array-formula

=iF(ISNUMBER(MATCH(9.99E+307,SEARCH(TRANSPOSE($A$1:$A$4),B170))),1,"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift Keys and hit Enter)

M.

 

[T. Valko]

This formula will search the B cell for a word and post an indicator if it contains the keyword.
=IF(ISNUMBER(SEARCH("citations",B170)),"1", "")

My obstacle is I need four of these that each search for 3-4 different keywords.

I've tried things like:
=IF(ISNUMBER(OR(SEARCH("citations",B170)), SEARCH("sources",B170))),"1", "")

But I get errors. Does anyone know how to get a formula that checks a cell for multiple keywords? I keep trying to put the OR in different places without success.

-David

Try one of these:

=IF(SUM(COUNTIF(B170,"*"&{"citations","sources"}&"*")),1,"")

=IF(OR(ISNUMBER(SEARCH({"citations","sources"},B170))),1, "")

 

[mdb001]

Can someone explain to me how and why the Match and Transpose Functions are being used here?
Thanks,
Mike

 

[shyy]

Search for multiple text strings in multiple cells in excel, part 2 | Get Digital Help - Microsoft Excel resource

=IF(COUNT(SEARCH({"citations","source"},B170)),"Yes","")

 

[jetboy2k]

Another possibility is nested IF/FIND statements:

=IF(ISERROR(FIND("String1",[Text to search])),IF(ISERROR(FIND("String2",[Text to search])),"String3","String2"),"String1")

Obviously,the syntax is a bit cumbersome, but this will allow you to search for as many strings as you want. The above formula assumes that every cell being searched will contain either String1, String2, or String3. If that's not the case (i.e., some searched cells don't contain the strings being searched for), the formula would need to be modified as follows:

=IF(ISERROR(FIND("String1",[Text to Search])),IF(ISERROR(FIND("String2",[Text to Search])),IF(ISERROR(FIND("String3",[Text to Search])),"No match found","String3"),"String2"),"String1")

As with the other solutions, [Text to Search] can reference another cell.

This solution, unlike the other solutions, allows you to see specifically which text string matched. If that's not what is needed, then this nested formulas within nested formulas solution is without question overkill, and not worth the "administrative cost."

 

[useindianproducts]

This Formula will find and give individual output for every search.

=IF(ISNUMBER(FIND("Query1",a1,1))=TRUE,"Answer1",IF(ISNUMBER(FIND("Query2",a1,1))=TRUE,"Answer2","Not Available"))

If No Query has been found, the query will give "Not Available".

 

[vinz27]

Hi, I have tried this but it doesnt seem to work. Can you please help

 

[vinz27]

This Formula will find and give individual output for every search.

=IF(ISNUMBER(FIND("Query1",a1,1))=TRUE,"Answer1",IF(ISNUMBER(FIND("Query2",a1,1))=TRUE,"Answer2","Not Available"))

If No Query has been found, the query will give "Not Available".

I have tried this but it doesnt work can you please help