[excel] Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel.

=IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","")

This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1.

All I'd like to do is search for more than one term at a time. Anyone know how to add an OR function into this so I can search for Gingrich OR Obama OR Romney etc... ?

In addition to the answer of @teylyn, I would like to add that you can put the string of multiple search terms inside a SINGLE cell (as opposed to using a different cell for each term and then using that range as argument to SEARCH), using named ranges and the EVALUATE function as I found from this link.

For example, I put the following terms as text in a cell, $G$1:

"PRB", "utilization", "alignment", "spectrum"

Then, I defined a named range named search_terms for that cell as described in the link above and shown in the figure below:

In the Refers to: field I put the following:

=EVALUATE("{" & TDoc_List!$G$1 & "}")

The above EVALUATE expression is simple used to emulate the literal string

{"PRB", "utilization", "alignment", "spectrum"}

to be used as input to the SEARCH function: using a direct reference to the SINGLE cell $G$1 (augmented with the curly braces in that case) inside SEARCH does not work, hence the use of named ranges and EVALUATE.

The trick now consists in replacing the direct reference to $G$1 by the EVALUATE-augmented named range search_terms.

It really works, and shows once more how powerful Excel really is!

Hope this helps.

Try using COUNT function like this

=IF(COUNT(SEARCH({"Romney","Obama","Gingrich"},C1)),1,"")

Note that you don't need the wildcards (as teylyn says) and unless there's a specific reason "1" doesn't need quotes (in fact that makes it a text value)

This will do it for you:

=IF(OR(ISNUMBER(SEARCH("Gingrich",C3)),ISNUMBER(SEARCH("Obama",C3))),"1","")

Given this function in the column to the right of the names (which are in column C), the result is:

Romney  
Gingrich    1
Obama       1