Extract multiple keywords from text string
Page 3 of 10 FirstFirst 12345 ... LastLast
Results 21 to 30 of 95

Thread: Extract multiple keywords from text string
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Peter_SSs View Post
    István
    That stops "apple" being reported in "pineapple" but not "ham" in "hamburger"
    Yes, but I mean that it stops reporting burger in hamburger.

  2. #22
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,469
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Aladin Akyurek View Post
    {=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&$A$1:$A$4&" "," "&B1&" ")),", "&$A$1:$A$4,""),""),1,2,"")}
    Aladin
    That no longer returns "fries" from the sample string.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Peter_SSs View Post
    Aladin
    That no longer returns "fries" from the sample string.
    Do we have an exhaustive list of keywords and punctuation marks that we can come across in the target string? Case by case evaluations are not very helpful regarding this class of problems.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #24
    Board Regular
    Join Date
    Jan 2012
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Gentlemen, many thanks for your help with this. I am in awe!

    If it helps, the keywords will populate column A (in this case A2:A21, because row 1 is the column headers) and will simply be text strings without punctuation, eg apple burger etc. It is possible that the text string could be two words eg red grapes etc but this is not a deal breaker if it presents problems. I have made the column headers equal the keyword cells in A2:A21 so that D1=A2, E1=A3 etc, ie if the keyword is changed the column header for the lookup will change to the identical text.

    Many thanks again

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

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by HughT View Post
    Gentlemen, many thanks for your help with this. I am in awe!

    If it helps, the keywords will populate column A (in this case A2:A21, because row 1 is the column headers) and will simply be text strings without punctuation, eg apple burger etc. It is possible that the text string could be two words eg red grapes etc but this is not a deal breaker if it presents problems. I have made the column headers equal the keyword cells in A2:A21 so that D1=A2, E1=A3 etc, ie if the keyword is changed the column header for the lookup will change to the identical text.

    Many thanks again
    Care to post these keywords and some of the difficult target strings?
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #26
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,469
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by HughT View Post
    If it helps, the keywords will populate column A ... and will simply be text strings without punctuation,..
    But your users are entering text in column B and they may use punctuation, which mostly causes problems with this sort of word-matching.

    I would feel more comfortable if you responded about the apple/pineapple type problem too. So far you seem to have ignored it but it is quite possible to a a healthy word turn up in a very unhealthy one (eg nuts / doughnuts) and the formula solutions that you seem to be accepting as doing what you want will return the row containing the unhealthy food.


    If the above can be solved then a list of rows of interest can be generated by formula.
    In the example below (still showing the apple/pineapple issue) I have a smaller data set so have just hidden 4 columns. In cell J1 you could set up a Data Validation to choose a value from A1:A?? (or from D1:??1).
    Then either of the K2 or L2 formulas copied down as far as you might ever need.
    The K2 formula requires Excel 2010 or later but is a standard-entry formula.
    The L2 formula will work in all versions but is an array formula. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
    For either K2 or L2 formula, the $1000 needs to be larger than the number of rows that will ever be used in columns B, C, D, ....

    Keywords

     ABCHIJKL
    1     Salad  
    2AppleToday I ate a burger with fries, and had an apple afterwards.Apple Fries Burger   Salad, pineapple & hamSalad, pineapple & ham
    3FriesToday I had eggs    Tuna salad & friesTuna salad & fries
    4SaladapplesApple     
    5BurgerSalad, pineapple & hamApple Salad     
    6 Tuna salad & friesFries Salad     
    7 Junk food      
    8 I ate 4 applesApple     
    9        
    10        

    Spreadsheet Formulas
    CellFormula
    C2=TRIM(D2&" "&E2&" "&F2&" "&G2)
    K2=IFERROR(INDEX(B$2:B$1000,AGGREGATE(15,6,(ROW(C$2:C$1000)-ROW(C$2)+1)/ISNUMBER(SEARCH(J$1,C$2:C$1000)),ROWS(K$2:K2))),"")
    L2{=IFERROR(INDEX(B$2:B$1000,SMALL(IF(ISNUMBER(SEARCH(J$1,C$2:C$1000)),ROW(C$2:C$1000)-ROW(C$2)+1),ROWS(L$2:L2))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Oct 15th, 2015 at 02:58 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #27
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,469
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Extract multiple keywords from text string

    Seems like this thread has died but I was just dealing with a similar issue in another thread & I think this is a way to deal with both the apple/pineapple and the punctuation issues. It does not address plurals.

    Keywords

     ABCDEF
    1  AppleFriesSaladBurger
    2Today I ate a burger with fries, and had an apple afterwards. AppleFries Burger
    3Today I had eggs     
    4Salad, pineapple & hamburger   Salad 
    5I ate 4 apples     
    6I ate an apple! Apple   

    Spreadsheet Formulas
    CellFormula
    C2=IF(IFERROR(AND(ABS(77.5-CODE(MID(UPPER(1&$A2),SEARCH(C$1,$A2),1)))>13,ABS(77.5-CODE(MID(UPPER($A2&1),SEARCH(C$1,$A2)+LEN(C$1),1)))>13),0),C$1,"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #28
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Peter_SSs View Post
    Seems like this thread has died but I was just dealing with a similar issue in another thread & I think this is a way to deal with both the apple/pineapple and the punctuation issues. It does not address plurals.

    Keywords

    A B C D E F
    1 Apple Fries Salad Burger
    2 Today I ate a burger with fries, and had an apple afterwards. Apple Fries Burger
    3 Today I had eggs
    4 Salad, pineapple & hamburger Salad
    5 I ate 4 apples
    6 I ate an apple! Apple

    Spreadsheet Formulas
    Cell Formula
    C2 =IF(IFERROR(AND(ABS(77.5-CODE(MID(UPPER(1&$A2),SEARCH(C$1,$A2),1)))>13,ABS(77.5-CODE(MID(UPPER($A2&1),SEARCH(C$1,$A2)+LEN(C$1),1)))>13),0),C$1,"")


    Excel tables to the web >> Excel Jeanie HTML 4
    It is a very good idea to identify punctuation marks at the end of the search word by converting the whole searched string to Uppercase and see if the end of the potential match changes or not.

    To further develop this method, consider the sentences below where apple and egg is not extracted:

    I like pineapple, but not apple.
    I like applesauce, but not apple itself.
    I gave the beggar an egg.

  9. #29
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,469
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by István Hirsch View Post
    To further develop this method, consider the sentences below where apple and egg is not extracted:

    I like pineapple, but not apple.
    I like applesauce, but not apple itself.
    I gave the beggar an egg.
    István

    Thanks for pointing out the (now obvious) flaw in my latest suggestion. For a moment I thought that I was onto something with that.

    Oh well, back to the drawing board - or the vba.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #30
    Board Regular
    Join Date
    Jan 2012
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Dear all

    Very many thanks for your continued help, and apologies for my absence. I have been out of the office for most of the past two weeks and unable to catch up. I will look at your kind responses in the next few days and try some suggestions out.

    Hugh

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
  •