+ Reply to Thread
Results 1 to 5 of 5

Extract all keywords from a cell based on a defined list

  1. #1
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    362

    Extract all keywords from a cell based on a defined list

    Hello experts,

    I would need your help to write a formula to extract all the fruits in a cell, based on the list provided in a range.

    For example

    Input
    A1----> Box1 has apples, orange Peach and banana
    A2----> apple, jackfruit, in box2 along with banana and peach
    A3----> Box no.3 has 100 guava,124 strawberrys

    Desired Result
    B1---->apples,orange,peach,banana
    B2---->apple,jackfruit,banana,peach
    B3---->guava,strawberry

    The list of fruits to check is in the range N2:N10

    Apple
    Orange
    Jackfruit
    Guava
    Strawberry
    Lemon
    Gooseberry
    Peach
    Banana

    and i might add more fruits in to the list. Attached excel sheet to make it clear.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,620

    Re: Extract all keywords from a cell based on a defined list

    Please Login or Register  to view this content.
    Use ...

    =MyConCat(A1,$N$2:$N$10)

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,538

    Re: Extract all keywords from a cell based on a defined list

    Hi,

    One way with a UDF

    1. Remove the trailing space in Guava in the column N list.
    2. Create a range name "lst_fruits" defined as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. Add the following UDF
    Please Login or Register  to view this content.
    4. In H11 enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy down.

    Just add new fruits to the list in column N
    Last edited by Richard Buttrey; 08-28-2016 at 06:01 AM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    362

    Re: Extract all keywords from a cell based on a defined list

    Thanks @Phuocam & @Richard Buttrey. Both solutions works brilliantly.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,620

    Re: Extract all keywords from a cell based on a defined list

    You are welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting based on keywords in changing list
    By enwoznick in forum Excel General
    Replies: 1
    Last Post: 07-27-2016, 11:11 AM
  2. [SOLVED] List of keywords to search from another sheet cell contains many other letters
    By wanian7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2015, 10:48 PM
  3. Macro to extract data based on defined criteria
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2014, 09:10 AM
  4. [SOLVED] Look for a list of keywords in a cell and then return the content of the found cell
    By RasmusClark in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-10-2013, 04:50 AM
  5. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  6. Replies: 3
    Last Post: 11-20-2012, 07:49 PM
  7. How to extract info based on any defined category
    By creamecake in forum Excel General
    Replies: 9
    Last Post: 11-16-2011, 01:56 PM

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