Excel Searching for multiple terms in a cell

Question

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

User · Answer

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

User · Answer

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

User · Answer

Another way   IF SUMPRODUCT -- NOT ISERR SEARCH   Gingrich   Obama   Romney   C1      gt 0  1        Also  if you keep a list of values in  say A1 to A3  then you can use   IF SUMPRODUCT -- NOT ISERR SEARCH  A 1  A 3 C1      gt 0  1        The wildcards are not necessary at all in the Search   function  since Search   returns the position of the found string

User · Answer

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      amp  TDoc List  G 1  amp         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

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

Examples related to excel

Examples related to excel-2007

Examples related to excel-formula

Examples related to excel-2010

Examples related to excel-2013