Page 4 of 10 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 95

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

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

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Peter_SSs View Post
    Edit: I hadn't seen your most recent post before submitting mine. I'll review mine once I've had a chance to study your last post.

    I think you would be starting to see from bosco_yip's suggestion that dealing with, say, 20 keywords a standard formula would also be pretty horrendous to leave to somebody else later. In fact that formula would have to get even longer to work properly. With the keyword sample list given & text "Today I ate a burger with fries, and had a pineapple afterwards." the formula returns the keyword "Apple", even though none was eaten.

    In fact that issue is the same with Aladin's formula at the moment so it also needs a tweak.

    My suggestion below, which also uses vba, has something of the opposite problem - refer row 5 of my screen shot where mine did not return Apple because that 'word' was not found in the text.

    There will also be the problem of other plurals where the initial ending changes. for example you may have "Cherry" in your list, so you would probably also need "Cherries"

    All a minefield really.

    Anyway, this is my attempt, also using a user-defined function. To implement ..

    1. Right click the sheet name tab and choose "View Code".

    2. In the Visual Basic window use the menu to Insert|Module

    3. Copy and Paste the code below into the main right hand pane that opens at step 2.

    4. Close the Visual Basic window.

    5. Enter the formula as shown in cell C1 in the screen shot below and copy down.

    6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

    Code:
    Function Keywords(s As String, rWords As Range) As String
      Dim kw As Object
      Dim i As Long
      
      With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "\b(" & Join(Application.Transpose(rWords), "|") & ")\b"
        If .test(s) Then
          Set kw = .Execute(s)
          For i = 1 To kw.Count
            Keywords = Keywords & ", " & kw(i - 1)
          Next i
        End If
      End With
      Keywords = Mid(Keywords, 3)
    End Function
    For comparison of where all the suggestions may have issues, I've included Aladin's and bosco's suggestions in columns D & E respectively.

    Sheet1

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

    Spreadsheet Formulas
    Cell Formula
    C1 =Keywords(B1,$A$1:$A$4)
    D1 {=REPLACE(aconcat(IF(ISNUMBER(SEARCH($A$1:$A$4,B1)),", "&$A$1:$A$4,""),""),1,2,"")}
    E1 =TRIM(IF(ISNUMBER(SEARCH($A$1,B1)),$A$1,"")&" "&IF(ISNUMBER(SEARCH($A$2,B1)),$A$2,"")&" "&IF(ISNUMBER(SEARCH($A$3,B1)),$A$3,"")&" "&IF(ISNUMBER(SEARCH($A$4,B1)),$A$4,""))
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    Hello Peter,
    I was looking for a solution to a requirement I have and I came across this thread.

    I tried the suggestions given by you and other posters but it did not work for me. I wanted to request your help on this matter.

    Kindly provide your inputs.

    My Requirement:


    1. Column A has Keywords. Example:
    Cell A1: FIELD-NAME-1
    Cell A2: FIELD-NAME-2
    so on...
    Cell A100: FIELD-NAME-100


    2. Column W has free form text, that may or may not have keywords from Column A.

    Cell W1: This is the free form text for FIELD-NAME-2
    Cell W2: This is the free form text for FIELD-NAME-1 and FIELD-NAME-100.
    Cell W3: This is the free form text for no fields.
    So on..
    Cell W20: This is the free form text for FIELD-NAME-1, FIELD-NAME-3, FIELD-NAME-10 and FIELD-NAME-50.


    3. I want the below in Column X (Basically check the Keywords in Column A in each of the cells in Column W and give the list of keywords.

    Cell X1: FIELD-NAME-2
    Cell X2: FIELD-NAME-1, FIELD-NAME-100
    Cell X3:
    So on..
    Cell X20: FIELD-NAME-1, FIELD-NAME-3, FIELD-NAME-10, FIELD-NAME-50.
    Row Nbr A W X
    1 FIELD-NAME-1 This is the free form text for FIELD-NAME-2 FIELD-NAME-2
    2 FIELD-NAME-2 This is the free form text for FIELD-NAME-1 and FIELD-NAME-100 FIELD-NAME-1, FIELD-NAME-100
    3 FIELD-NAME-3 This is the free form text for no fields
    So on.. so on.. so on.. So on..


    Solution (Did not work):

    Followed your solution.

    a. Save excel sheet in .xlsm type. (I have excel 2010).

    b. Inserted the below module in vba

    Function Keywords(s As String, rWords As Range) As String
    Dim kw As Object
    Dim i As Long

    With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Global = True
    .Pattern = "\b(" & Join(Application.Transpose(rWords), "|") & ")\b"
    If .test(s) Then
    Set kw = .Execute(s)
    For i = 1 To kw.Count
    Keywords = Keywords & ", " & kw(i - 1)
    Next i
    End If
    End With
    Keywords = Mid(Keywords, 3)
    End Function

    c. In X1 Cell I put the below formula
    =Keywords(W1,$A$1:$A$100)
    X2 Cell I put the below formula
    =Keywords(W2,$A$1:$A$100)
    So on...
    W20 Cell I put the below forumula
    =Keywords(W20,$A$1:$A$100)

    d. However the Cells in Column X show blank and not the expected result.

    Not sure what I am doing wrong. Kindly help.





  2. #32
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Dear Mr. Peter_SSs,

    I have more or less same problem, want to extract the exact words from B1 by searching from A1:A10 list and display in column C. Please help me Sir.

    It content ;

    COLUMN A (LIST )

    MDB
    SMDB
    ESMDB
    FESMDB
    /f
    SWITCH
    SWITCHGEAR

    COLUMN B

    testing for MDB, DB
    I am testing SMDB, MDB
    Looking for ESMDB
    Checking for FESMDB
    This is for cable /f

    COLUMN C (Result)



  3. #33
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by iazeemkhan View Post
    I have more or less same problem, want to extract the exact words from B1 by searching from A1:A10 list and display in column C. Please help me Sir.
    Your sample data is not entirely clear and you have not shown the expected results and their layout. Can you clarify both the sample data and the expected results?
    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

  4. #34
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by Peter_SSs View Post
    Your sample data is not entirely clear and you have not shown the expected results and their layout. Can you clarify both the sample data and the expected results?

    Dear Sir,

    Many thanks for your reply, Sir, actually I want to extract exact names from Description column and put in a Result Column (The Exacts Names are mentioned in the LIST below). Need command to read Item from Name List, search in Description and display in Result Column). Count column is only counting how many item there in Result i.e. Cable Ladder = 1 and MDB,Switch = 2 and so on......

    Sir, Want to extract exact item name from the name list with comma in Result column. Many thanks in advance Sir.

    Main Data sample With Result and Count of Result Item

    Description Result Count
    HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder Cable Ladder 1
    LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-02(1887.7kW) 11 HV Switchgear 11 HV Switchgear, MDB, Switch 3
    LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB MDB, Switch 2

    Name List
    11 HV Switchgear
    MDB
    Switch
    Cable Ladder
    Access control
    ATS
    BGM
    Central battery
    ......... many more names are there

  5. #35
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by iazeemkhan View Post
    Dear Sir,

    Many thanks for your reply, Sir, actually I want to extract exact names from Description column and put in a Result Column (The Exacts Names are mentioned in the LIST below). Need command to read Item from Name List, search in Description and display in Result Column). Count column is only counting how many item there in Result i.e. Cable Ladder = 1 and MDB,Switch = 2 and so on......

    Sir, Want to extract exact item name from the name list with comma in Result column. Many thanks in advance Sir.
    Did you try the user-defined function from post #7 ?

    Sheet1

    ABC
    1Name ListDescriptionResult
    211 HV SwitchgearHIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Laddercable Ladder
    3MDBLOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-02(1887.7kW) 11 HV SwitchgearSwitch, MDB, 11 HV Switchgear
    4SwitchLOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDBSwitch, MDB
    5Cable Ladder
    6Access control
    7ATS
    8BGM
    9Central battery
    10

    Spreadsheet Formulas
    CellFormula
    C2=Keywords(B2,A$2:A$8)


    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #36
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Fantastic Sir, but the only problem is that words are repeating(Please see below in the table) i.e. Security,Access control and Earthing is showing more then ONE TIME, I need them to appear only one time in the Result column.



    Quote Originally Posted by Peter_SSs View Post
    Did you try the user-defined function from post #7 ?

    Sheet1

    A B C
    1 Name List Description Result
    2 11 HV Switchgear SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge
    SECURITY, CCTV, security
    3 MDB EARTHING AND BONDING SYSTEM INSTALLATION; EQUIPMENT; Earthing and bonding system, including supports, final point-wiring, containment, test points, air termination network, strike pads, cables and accessories; Earthing for power
    EARTHING, Earthing, Earthing
    4 Switch ACCESS CONTROL SYSTEM INSTALLATION; EQUIPMENT; Access control Panel; Access control Panel
    ACCESS CONTROL, Access control, Access control
    5 Cable Ladder
    6 Access control
    7 ATS
    8 BGM
    9 Access control
    10 Earthing

    Spreadsheet Formulas
    Cell Formula
    C2 =Keywords(B2,A$2:A$8)


    Excel tables to the web >> Excel Jeanie HTML 4

  7. #37
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by iazeemkhan View Post
    Fantastic Sir, but the only problem is that words are repeating(Please see below in the table) i.e. Security,Access control and Earthing is showing more then ONE TIME, I need them to appear only one time in the Result column.
    Pity that wasn't stated, or an example like that given, earlier.

    Try changing the line in blue.
    Code:
    For i = 1 To kw.Count
      If InStr(1, ", " & Keywords & ", ", ", " & kw(i - 1) & ", ", 1) = 0 Then Keywords = Keywords & ", " & kw(i - 1)
    Next i
    Also, I forgot to state earlier, that the column with the list of keywords must not contain any blank cells. If that could happen, then the code would need some further modifications.


    BTW, best not to quote whole long posts as it makes the thread harder to read/navigate. If you want/need to quote, quote small, relevant parts only.
    Last edited by Peter_SSs; Jul 23rd, 2019 at 10:19 PM.
    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

  8. #38
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Dear Sir,

    Its done, Many many many thanks........Sir.

  9. #39
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by iazeemkhan View Post
    Dear Sir,

    Its done, Many many many thanks........Sir.
    You are very welcome. Glad we got there in the end.
    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

  10. #40
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Dear Sir,

    I have data with description in column A, column B is Component name, Result will be in C column, need to search TEXT in Description (B Column) from given D,E,F,G,H and on..... If found any one of them in Description, then display Component name which is there in column B. Example below;

    A B C D E F G H
    Description Component Result
    HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Switchgear HV Switchgear HV Switchgear MV Panels MV Switchgear 11 KV Switchgear RMU MVS AND ON...............
    SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge Access control Access control Access control panel Card Reader Push button for exit Mortise Lock Input Module AND ON...............


    Thank you Sir, in advance.

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
  •