Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 45

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

  1. #21
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by laviz View Post
    Hi, the below formula is really helpful.
    Question - is there a way to bring the word that was found, instead of "Yes" / "No"?
    thanks a lot!
    Try...

    =LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)
    Assuming too much and qualifying too much are two faces of the same problem.

  2. #22
    New Member
    Join Date
    Jul 2015
    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

    WOWWWWW excellent, thanks a lot!

  3. #23
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by laviz View Post
    WOWWWWW excellent, thanks a lot!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #24
    New Member
    Join Date
    Aug 2015
    Posts
    1
    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
    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.

    This is almost exactly as I need too, so thank you very much for the reply.

    If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)

  5. #25
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by mickarose View Post
    This is almost exactly as I need too, so thank you very much for the reply.

    If I needed the return to detail all keywords found not just the first, could it be done? ie for row A6 in example given the return would be a cell with both key numbers in a cell comma separated perhaps? (key2, key4)
    That can be done either each return in a cell of its own or comma-separated in a single cell using ACONCAT, a function in VBA.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #26
    New Member
    Join Date
    Sep 2015
    Posts
    1
    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 Aladin Akyurek View Post
    Try...

    =LOOKUP(9.99999999999999E+307,SEARCH(KeywordTable,E2),KeywordTable)
    I joined the forum just to say THANK YOU!!

  7. #27
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by aquariaj1 View Post
    I joined the forum just to say THANK YOU!!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #28
    New Member
    Join Date
    Oct 2015
    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
    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.
    Thank you! Works like a charm!

    One more question:

    How can it be done with another column: E(Answers) ?

    E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B


    A
    B
    C
    D
    E
    1
    Text
    Answer
    Keywords
    Answers
    2
    text key3 text
    Answer3
    Key1
    Answer1
    3
    key4 text
    Answer4
    Key2
    Answer2
    4
    text key1
    Answer1
    Key3
    Answer3
    5
    text text
    Not Found
    Key4
    Answer4
    6
    text key2 key4
    Answer2
    Key5
    Answer5
    Last edited by macci; Oct 20th, 2015 at 08:23 PM.

  9. #29
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by macci View Post
    Thank you! Works like a charm!

    One more question:

    How can it be done with another column: E(Answers) ?

    E has answers for keywords. So, IF A HAS TEXT OF D(Keywords) THEN COPY THE E(Answers) INTO B


    A
    B
    C
    D
    E
    1
    Text
    Answer
    Keywords
    Answers
    2
    text key3 text
    Answer3
    Key1
    Answer1
    3
    key4 text
    Answer4
    Key2
    Answer2
    4
    text key1
    Answer1
    Key3
    Answer3
    5
    text text
    Not Found
    Key4
    Answer4
    6
    text key2 key4
    Answer2
    Key5
    Answer5
    In B2 control+shift+enter, not just enter, and copy down:

    =IFERROR(INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH(" "&$D$2:$D$6&" "," "&A2&" ")),0)),"Not Found")
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #30
    New Member
    Join Date
    Oct 2015
    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


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
  •