+ Reply to Thread
Results 1 to 7 of 7

Extract one of five words from one cell into another

  1. 01-20-2017, 08:56 PM #1
    M12NIX
    M12NIX is offline
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Playa Del Carmen, Mexico
    MS-Off Ver
    2016
    Posts
    111

    Extract one of five words from one cell into another

    Can anyone help me with this?

    I have a sheet that displays entries as shown below. All I need are the words Sapphire, Ruby, Emerald, Diamond or Elite in another cell. Everything else can go !

    I am at a loss!

    MX-CUN-PRES-DIA-ELITE-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-RUBY-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-SAPPHIRE-US-EX
    MX-CUN-PRES-DIAMOND-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-DIA-ELITE-EX
    MX-CUN-PRES-DIA-ELITE-PR
    MX-CUN-PRES-RUBY-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-EMERALD-US-EX
    MX-CUN-PRES-DIA-ELITE-EX
    MX-CUN-PRES-DIAMOND-US-EX
    Last edited by M12NIX; 01-20-2017 at 09:16 PM.
    Reply With QuoteRegister To Reply
  2. 01-20-2017, 09:17 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Extract one of five words from one cell into another

    One way, assuuming all strings begin with MX-CUN-PRES-

    =LEFT(SUBSTITUTE(MID(A1,13,255),"DIA-",""),FIND("-",SUBSTITUTE(MID(A1,13,255),"DIA-",""))-1)

    If they do not all begin that way, post REPRESENTATIVE examples.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 01-20-2017, 09:20 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract one of five words from one cell into another

    Try this...

    List the keywords in a range of cells like D2:D6.

    Data Range
    A
    B
    C
    D
    1
    2
    MX-CUN-PRES-DIA-ELITE-EX
    Elite
    ------
    Sapphire
    3
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    Ruby
    4
    MX-CUN-PRES-RUBY-US-EX
    Ruby
    Emerald
    5
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    Diamond
    6
    MX-CUN-PRES-SAPPHIRE-US-EX
    Sapphire
    Elite
    7
    MX-CUN-PRES-DIAMOND-US-EX
    Diamond
    8
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    9
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    10
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    11
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    12
    MX-CUN-PRES-DIA-ELITE-EX
    Elite
    13
    MX-CUN-PRES-DIA-ELITE-PR
    Elite
    14
    MX-CUN-PRES-RUBY-US-EX
    Ruby
    15
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    16
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    17
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    18
    MX-CUN-PRES-EMERALD-US-EX
    Emerald
    19
    MX-CUN-PRES-DIA-ELITE-EX
    Elite
    20
    MX-CUN-PRES-DIAMOND-US-EX
    Diamond


    Then, this formula entered in B2 and copied down:

    =IFERROR(LOOKUP(1000,SEARCH(D$2:D$6,A2),D$2:D$6),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 01-20-2017, 09:43 PM #4
    M12NIX
    M12NIX is offline
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Playa Del Carmen, Mexico
    MS-Off Ver
    2016
    Posts
    111

    Re: Extract one of five words from one cell into another

    When I filter the rows I require they do all begin with MX-CUN-PRES- so thank you so much. I am, however at a loss as to what you mean by 'post REPRESENTATIVE examples'. Can you clarify please?
    Reply With QuoteRegister To Reply
  5. 01-20-2017, 09:47 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Extract one of five words from one cell into another

    What I asked was that if the strings DID NOT all begin that way (MX-CUN etc,) you should provise examplea of the different beginnings. However, Tony's solution will work whatevwr the string begins with.
    Reply With QuoteRegister To Reply
  6. 01-25-2017, 07:25 PM #6
    M12NIX
    M12NIX is offline
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Playa Del Carmen, Mexico
    MS-Off Ver
    2016
    Posts
    111

    Re: Extract one of five words from one cell into another

    Unfortunately I have discovered that 3 different offices edit this file and as a result there are many ways in which this can be entered. For now Tony's suggestion fits best and I will investigate further when I have met the teams to try and define what should and shouldn't be entered. Thank you so much for your assistance gentlemen.
    Reply With QuoteRegister To Reply
  7. 01-25-2017, 08:41 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract one of five words from one cell into another

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to extract first 3 words from another cell
    By papusale in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-03-2015, 08:53 AM
  2. Need to extract only words between two dashes in other cell
    By hrdpgajjar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-15-2014, 08:38 AM
  3. Need to extract last 4 words from a cell
    By DrFistington in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-13-2014, 05:12 PM
  4. [SOLVED] extract last 2 words of cell
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-26-2014, 04:09 PM
  5. [SOLVED] Need to extract words or text from a cell containing numeric / alpha-numeric and words
    By vatsan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-25-2014, 02:00 AM
  6. [SOLVED] Extract first two words of a cell
    By letangerang58 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2013, 01:12 PM
  7. Extract particular words from a cell
    By DeepakS in forum Excel General
    Replies: 2
    Last Post: 08-22-2011, 08:58 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1