Page 1 of 5 123 ... LastLast
Results 1 to 10 of 45

Thread: Search for multiple text strings simultaneously in a single cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search for multiple text strings simultaneously in a single cell

    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.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    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.

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    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.

  4. #4
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    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!

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    I added a helper column, if you can do that try out this
    Excel 2010
    JKLMNO
    6datalook upColumn1
    7the howyes
    8and whoyes
    9yesthey janno
    10them janeno
    11is doeno
    12and heno
    13whyhimno
    14howtheno
    15whowhoyes

    Sheet1



    Worksheet Formulas
    CellFormula
    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")

    Array Formulas
    CellFormula
    J9{=IF(O7:O15="yes","yes","No")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    Quote Originally Posted by geekylawyer View Post
    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.

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    Quote Originally Posted by geekylawyer View Post
    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.

  8. #8
    Board Regular
    Join Date
    Feb 2014
    Location
    Des Moines, IA
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    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?

  9. #9
    New Member
    Join Date
    Nov 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    Quote Originally Posted by Marcelo Branco View Post
    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.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,937
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell

    Quote Originally Posted by JohnB2764 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •