+ Reply to Thread
Results 1 to 21 of 21

Extract Last Word

  1. #1
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Question Extract Last Word

    Hi,

    I want to extract the last word of a cell but it is sometimes compound and sometimes not. It depends on the cell that matches in the VLOOKUP.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Extract Last Word

    This formula extracts last word of text in cell A1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    Hi Sintek,

    It was helpful but what if some of the words are compound? After that I need to make a VLOOKUP with the word that was extracted so that it can match another sheet and result in a code.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    what do you mean by compound?
    provide some samples please
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    Hi FDibbins,

    For example, sometimes the word that I need is Toaster Pastry (two words), and sometimes it's just Granola (one word). I know what you're thinking, how would it know when it's compound and when it's not. Well in the VLOOKUP the exact word that I need is what i'm trying to extract. Maybe i'm using the wrong function. Could it be a search?


    Sheet 1 A1
    Product
    Granola
    Toaster Pastry

    Sheet 2 C1 C2
    Product Code
    Granola 123
    Toaster Pastry 321


    So I want it to find the Granola word or Toaster Pastry word at Sheet 2 C1 on Sheet 1 A1 and just give me the C2 as a result. If it finds the word Toaster Pastry then result in 321.

    I hope you understand me. My brain is fried already
    Last edited by nicolmele; 03-22-2017 at 03:49 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    It might help if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Extract Last Word

    Quote Originally Posted by FDibbins View Post
    what do you mean by compound?
    provide some samples please
    At a loss here Ford, would love to see this one solved.

  8. #8
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    Attached. I hope I did it right.
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    Will you ever have 4-word (or more) combos?

    penut grated chocolate granola?

  10. #10
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    Yes, but it doesn't really matter. The only word that I need to match in the lookup is the last word (Granola, Toaster Pastry) The other part is just the description of the product. Which is why I was trying to extract the last word but got stucked.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    no, actually it does matter.

    This is what I have so far...
    =INDEX(Sheet2!$E$2:$E$4,IFERROR(MATCH(MID(A2,FIND(" ",A2)+1,99),Sheet2!$F$2:$F$4,0),MATCH(MID(A2,FIND("@",SUBSTITUTE(A2," ","@",2))+1,99),Sheet2!$F$2:$F$4,0)))
    but I just thought of a way to work around how many words there are

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    =INDEX(Sheet2!$E$2:$E$4,IFERROR(MATCH(MID(A2,FIND(" ",A2)+1,99),Sheet2!$F$2:$F$4,0),MATCH(MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99),Sheet2!$F$2:$F$4,0)))

  13. #13
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    I tried it and it worked in every product that was on the example but the granola. The things is that it's a product description that can have many many many words. But is has a structure and only the product is in the last part of the description. I thought about doing it the other way around. Trying to find the word from the sheet 2 on the product description cell in the sheet one but got stuck. What you can count on is that the word is the last and that it could only be one or two.

    I really need to do this. If I can't i'm going to have to do it manually on 44k products.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    It worked for me?
    A
    B
    C
    D
    1
    Product Description Result that I need Mine
    2
    Chocolotta Toaster Pastry
    G2605
    G2605
    3
    Penut Chocolate Granola
    G1605
    G1605
    4
    Raw Strawberry
    F1025
    F1025
    5
    Penut grated Chocolate Granola G1605

    D2=INDEX(Sheet2!$E$2:$E$4,IFERROR(MATCH(MID(A2,FIND(" ",A2)+1,99),Sheet2!$F$2:$F$4,0),MATCH(MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99),Sheet2!$F$2:$F$4,0)))
    copied down.

    If that isnt working for you, upload the sample where it doesnt work please?

  15. #15
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    Uploaded. C3 is not working.
    Attached Files Attached Files

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    aahh yes, forgot top mention, you have a trailing space in A4

  17. #17
    Registered User
    Join Date
    03-22-2017
    Location
    Puerto Rico
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Extract Last Word

    This is the original file, the formula is not really working . Please ignore the sloppiness, I've been working on it the whole day.


    Thank You so much for your help Ford.
    Attached Files Attached Files
    Last edited by nicolmele; 03-22-2017 at 05:22 PM.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    I have changed to this now, but there are some items in column C that just do not exist in sheet 3...
    D1161=INDEX(Sheet3!$E$1:$E$230,IFERROR(MATCH(MID(C1161,FIND("@",SUBSTITUTE(C1161," ","@",LEN(C1161)-LEN(SUBSTITUTE(C1161," ",""))-1))+1,99),Sheet3!$F$1:$F$230,0),MATCH(MID(C1161,FIND("@",SUBSTITUTE(C1161," ","@",LEN(C1161)-LEN(SUBSTITUTE(C1161," ",""))))+1,99),Sheet3!$F$1:$F$230,0)))

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Last Word

    nicolmele, I have a quick question ... and I am not sure where I'm going with this ... but are you locked into the layout of the lookup values in Sheet3?

    It would seem that a two column layout would make things easier. You could remove duplicates and maybe have a workable lookup table.
    Dave

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract Last Word

    Something to consider, Dave

    Also, instead of that big cumbersome formula, I thought about breaking the FIND's out into 2 helpers - makes it easier to troubleshoot, and also shows what is being searched for.

    With that in mind, on sheet 1...
    L1161=MID(C1161,FIND("@",SUBSTITUTE(C1161," ","@",LEN(C1161)-LEN(SUBSTITUTE(C1161," ",""))-1))+1,99)
    M1161=MID(C1161,FIND("@",SUBSTITUTE(C1161," ","@",LEN(C1161)-LEN(SUBSTITUTE(C1161," ",""))))+1,99)
    D1161=INDEX(Sheet3!$E$1:$E$230,IFERROR(MATCH(L1161,Sheet3!$F$1:$F$230,0),MATCH(M1161,Sheet3!$F$1:$F$230,0)))
    all copied down

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Last Word

    Quote Originally Posted by FlameRetired View Post
    nicolmele, I have a quick question ... and I am not sure where I'm going with this ... but are you locked into the layout of the lookup values in Sheet3?

    It would seem that a two column layout would make things easier. You could remove duplicates and maybe have a workable lookup table.
    Never mind. No that won't help.

+ 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. Extract a word in the workbook's filename and add this word to another word
    By cyberjoad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2016, 03:54 AM
  2. [SOLVED] How to extract word
    By chief_abound in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2015, 07:06 AM
  3. Extract last word
    By blackcat in forum Excel General
    Replies: 2
    Last Post: 03-07-2015, 11:26 AM
  4. Extract last word from a string and next to last word and so on.
    By ejbeckwith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2013, 08:51 AM
  5. Search for a word in a column and extract the word
    By Abishekar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2012, 09:30 AM
  6. [SOLVED] Extract last word
    By Aland2929 in forum Excel General
    Replies: 2
    Last Post: 03-24-2012, 07:34 AM
  7. Extract a text from word and place it en excel or word?
    By Elegidos in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2009, 06:14 AM

Tags for this Thread

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