Page 2 of 10 FirstFirst 1234 ... LastLast
Results 11 to 20 of 95

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

  1. #11
    Board Regular
    Join Date
    Jan 2012
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Thank you for your most elegant solution.

    For searching column C by individual keyword I was wondering if an Index / Match or Indirect function might work, but how?


    Many thanks

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by HughT View Post
    For searching column C by individual keyword I was wondering if an Index / Match or Indirect function might work, but how?
    I'm not really sure what you are after. Taking the sample data in post #10, and assuming the row values are concatenated in column C ...

    What result (& where) would you expect if the word of interest was "apple"?
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #13
    Board Regular
    Join Date
    Jan 2012
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Results will be concatenated in column C, and from the example above, the first result will be Apple, Fries, Burger. Each cell in column C will contain a range of results from the available keywords derived from the freetext cell (potentially all of them).

    What I want a user to be able to do is filter the rows by a single keyword, so for example by selecting 'Apple' they would see all the rows where 'Apple' appears in column C, even if the cell also contains other keywords such as , 'Apple Fries Burger' from the example above. It is possible to do this using Filters / Text Filters / Contains but this requires knowledge of how to do this and is not very user friendly, and is dependent on a user inputting the exact text string to obtain a match.

    I was wondering if it was possible to use the keywords range as a lookup table to drive some sort of Index/Match or Indirect function, but at that point my Excel-fu gave up!

    Many thanks for your help with this.

  4. #14
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by HughT View Post
    Results will be concatenated in column C, and from the example above, the first result will be Apple, Fries, Burger. Each cell in column C will contain a range of results from the available keywords derived from the freetext cell (potentially all of them).

    What I want a user to be able to do is filter the rows by a single keyword, so for example by selecting 'Apple' they would see all the rows where 'Apple' appears in column C, even if the cell also contains other keywords such as , 'Apple Fries Burger' from the example above. It is possible to do this using Filters / Text Filters / Contains but this requires knowledge of how to do this and is not very user friendly, and is dependent on a user inputting the exact text string to obtain a match.

    I was wondering if it was possible to use the keywords range as a lookup table to drive some sort of Index/Match or Indirect function, but at that point my Excel-fu gave up!

    Many thanks for your help with this.
    If the users are incapable of using AutoFilter and you insist on not having any vba then there will be no Filter as such. What would be possible is to produce another table somewhere using formulas that extracted just the rows of interest.

    However, to indicate what word they are interested in they would have to type it somewhere or choose it from a data validation list drop-down. Typing it would still be dependent on the user "inputting the exact text string", something I think you doubt. If the user can't handle choosing from an AutoFilter drop-down then I'm not sure they could handle choosing from a Data validation drop-down. I'm not sure where that leaves you.


    In any case, when you say "...they would see all the rows where...", do you just mean the column B values or do you mean all 20+ columns of your sheet?
    Last edited by Peter_SSs; Oct 13th, 2015 at 08:18 AM. Reason: typo
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #15
    Board Regular
    Join Date
    Jan 2012
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Peter

    Thanks for working with this.

    Ideally I would like users to be able to select from a drop down using the keywords range, in a similar way to Data Validation. This means that they could only select what was available, and it would be simple to do.

    If this would require vba then so be it, and I could write instructions as to how to amend the vba (eg if they needed to extend beyond 20 items) and then save the project. Unfortunately I am not vba literate so it could not go much beyond that.

    However extracting the table with selected rows to somewhere else also sounds very interesting as it means that they would have a report on what they had selected too.

    Many thanks!

  6. #16
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by HughT View Post
    If this would require vba then so be it,
    Hmm, but if we use vba for this, we could also use it to produce the list in column C?


    What about this?
    Quote Originally Posted by Peter_SSs View Post
    In any case, when you say "...they would see all the rows where...", do you just mean the column B values or do you mean all 20+ columns of your sheet?
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #17
    Board Regular
    Join Date
    Jan 2012
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    The organisation who I work for is generally anti anything vba because it means that when someone moves on and a change has to be made, there is nobody there who understands vba to do it and the whole system falls apart. We have a lot of legacy spreadsheets which people depend on which aren't supported. What we have built up to now works beautifully without vba so someone like me could easily amend the formulas to extend the ranges etc.

    However if what we need could only be achieved by vba that is ok because if the worst came to the worst the availability of Filters / Text Filters / Contains is still there, just not as simple or quick to use for someone with little or no Excel understanding.

    Thank you for being so helpful with this.

    Regarding viewing the columns, this isn't an issue because it is simply a matter of hiding them and protecting the worksheet so that users can only enter data in the data entry cells and perform the search / filter function against specific keywords.

  8. #18
    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
    In that case, you can use them (headers) for a much simpler formula in your columns.

    Note that you still have the issue of the apple showing up when pineapple is in the text & apple is not, but you seem happy to accept that. (same issue could occur with "ham" and "hamburger" etc)

    Formula in D2 is copied across and down.

    Keywords

    B C D E F G
    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 apples Apple
    5 Salad, pineapple & ham Apple Salad
    6 I ate 4 apples Apple

    Spreadsheet Formulas
    Cell Formula
    D2 =IF(SEARCH(D$1,$B2&D$1)($B2),D$1,"")


    Excel tables to the web >> Excel Jeanie HTML 4

    It seems pineapple – apple and hamburger – burger problem can be solved by a little modification:

    =IF(SEARCH(" "&D$1," "&$B2&" "&D$1)<len($b2),d$1,"")
    Last edited by Peter_SSs; Oct 13th, 2015 at 04:42 PM. Reason: Fixed < problem

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

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Aladin Akyurek View Post
    Row\Col
    A
    B
    C
    1
    Apple Today I ate a burger with fries, and had an apple afterwards. Apple, Fries, Burger
    2
    Fries
    3
    Salad
    4
    Burger
    5


    In C1 control+shift+enter, not just enter:

    =REPLACE(aconcat(IF(ISNUMBER(SEARCH($A$1:$A$4,B1)),", "&$A$1:$A$4,""),""),1,2,"")

    For this formula to work, you need to add the VBA code for ACONCAT to your workbook as a module, using Alt+F11...

    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    One should adjust the formula to the input, not dismiss out of hand..

    {=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&$A$1:$A$4&" "," "&B1&" ")),", "&$A$1:$A$4,""),""),1,2,"")}

    Note that ACONCAT is a generic function, therefore preferable.just for that reason.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #20
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by István Hirsch View Post
    It seems pineapple – apple and hamburger – burger problem can be solved by a little modification:

    =IF(SEARCH(" "&D$1," "&$B2&" "&D$1)
    István
    That stops "apple" being reported in "pineapple" but not "ham" in "hamburger"
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •