+ Reply to Thread
Results 1 to 11 of 11

find word(s) within text string that matches a list of keywords

  1. #1
    Registered User
    Join Date
    06-08-2011
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    8

    Question find word(s) within text string that matches a list of keywords

    Hi Everyone,

    This is my first post.

    Although the solution I seek seems rather straightforward, i'm beginning to wonder if it's even technically possible since I can't find a formula that works - using lookup, find, or a combination of various functions.

    Basically, I have a column of text strings in worksheet one which I need to check for the presence of keywords which is listed in another column in worksheet two (the keyword list).

    So if any word from the keyword list is found in, say, cell A1 of sheet one, the cell to its right (B1) should have a formula to display either the first match (or if possible, all the matched words).

    Factors to be taken into consideration are:
    - the column of text in sheet one may be run for up to 500 rows
    - the keyword list may contain up to 3000 keywords (3000 rows)
    - the match must to be case sensitive
    - the match must be for whole words (and not match "office" to "off")
    - there are punctuations in the text in sheet one.

    I have also attached an example of how my data is organised.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: find word(s) within text string that matches a list of keywords

    Hello,

    See the attached.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    06-08-2011
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    8

    Thumbs up Re: find word(s) within text string that matches a list of keywords

    Hi Haseeb,

    This is just amazing!
    I'm... speechless

    Thanks!!

  4. #4
    Registered User
    Join Date
    06-10-2011
    Location
    Romania
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: find word(s) within text string that matches a list of keywords

    Hi ! Great tools Haseeb !
    Good for me but with few exceptions ... How do I disable "Match Case" and "Find Whole Words Only" ? (example attached)
    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: find word(s) within text string that matches a list of keywords

    Hello dan5588, Welcome to the forum!

    Please review the forum rule.

    Please do not post a question in the thread of another member, start your own thread & give a link of this thread in the new thread.

    http://www.excelforum.com/forum-rule...rum-rules.html

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-04-2012
    Location
    Scarbourough
    MS-Off Ver
    Excel 2000
    Posts
    1

    Re: find word(s) within text string that matches a list of keywords

    I have a similar case but dont understand your code so wouldnt know how to start editing it

    A2:A20 is a list of countries in the EU
    For each paypal entry, I just want to know if any of them is in the string(multiple words, ie a street adddress) column R amd if so return TRUE in column S

    Can you possibly give me some guidance on how to do this

    Thanks

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: find word(s) within text string that matches a list of keywords

    Hi phil4v19, Welcome to the forum!

    Please read the forum rules & start your own thread.

    Forum Rules

    Thank you.

  8. #8
    Registered User
    Join Date
    05-22-2012
    Location
    Cape Town South Africa
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: find word(s) within text string that matches a list of keywords

    thx for this, excel-ent ;-))

  9. #9
    Registered User
    Join Date
    07-11-2012
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: find word(s) within text string that matches a list of keywords

    Thanks all, this works well - but is there a VBA equivalent to the search() function?

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find word(s) within text string that matches a list of keywords

    Nigellegg,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Registered User
    Join Date
    07-11-2012
    Location
    Bristol, UK
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: find word(s) within text string that matches a list of keywords

    My apologies, arlu1201. I thought that as it was a follow on question asking basically the same thing, it would be ok.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1