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.
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.
This formula extracts last word of text in cell A1
Formula: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!!!
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.
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
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.
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.
Attached. I hope I did it right.
Will you ever have 4-word (or more) combos?
penut grated chocolate granola?
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.
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
=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)))
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.
It worked for me?
A B C D 1Product Description Result that I need Mine 2Chocolotta Toaster Pastry G2605G2605 3Penut Chocolate Granola G1605G1605 4Raw Strawberry F1025F1025 5Penut 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?
Uploaded. C3 is not working.
aahh yes, forgot top mention, you have a trailing space in A4
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.
Last edited by nicolmele; 03-22-2017 at 05:22 PM.
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)))
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks