+ Reply to Thread
Results 1 to 5 of 5

Search text in cells, find matching text based on list, remove all but one entry

  1. #1
    Registered User
    Join Date
    12-14-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Search text in cells, find matching text based on list, remove all but one entry

    Hi, I have very basic excel knowledge, and wondered if the following was possible, please :

    I have an excel 2003 xls file. One columns conatins cells of words separated with commas and a space.
    I have another column containing a list of 'allowed' words.
    What I'd like to do is check the comma-separated words in each cell to find the first word that is on the 'allowed' list. That word will stay in the cell, and all others are deleted.
    If a cell contains no 'allowed' words then fill that cell with a colour (or mark it someway) so I can see it easily after the search.

    I have attached an example xls.

    Thanks in advance for any help
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,185

    Re: Search text in cells, find matching text based on list, remove all but one entry

    Try it in C2:
    =INDEX($A$2:$A$102,MATCH(1,SEARCH($A$2:$A$102,B2)/SEARCH($A$2:$A$102,B2),0))
    Drag down

  3. #3
    Registered User
    Join Date
    12-14-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Search text in cells, find matching text based on list, remove all but one entry

    Thanks for the quick reply. I copied and pasted that into C2 down to C681 but the results were #Value! or #N/A
    I've attached the resulting xls.
    If it works for you, could you upload your version of the xls so I can see how it works please?

    Thanks !
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,185

    Re: Search text in cells, find matching text based on list, remove all but one entry

    Oh, sorry. This is array formula you must confirm by holding Ctrl-Shift, then press enter. If it is done in a right way, you with see a couple of curse bracket {} wraping the formula in the formula bar.

  5. #5
    Registered User
    Join Date
    12-14-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Search text in cells, find matching text based on list, remove all but one entry

    Bebo, thanks!
    I have clicked the star

+ 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