I have a string of text with a colour in it. They are seperated by a space. I want to extract only the colour. what would be the formula? i.e. sample material Blue. Need to extract the Blue. Colour is always at the end.
I have a string of text with a colour in it. They are seperated by a space. I want to extract only the colour. what would be the formula? i.e. sample material Blue. Need to extract the Blue. Colour is always at the end.
are they always only 2 words like your example
ie. material Blue
color Red
box White
if so you could use text to columns, which would be the easiest
if not use this formula: (obviously it's looking at cell A2)
=IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)
got it with a simple google search - excel extract last word in string
http://www.excel-user.com/2010/10/ge...om-string.html
Hi Trigger99,
Here is a formula that will return the last word in a string of text.
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
See attached for the sample.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin, I have been trying to pull your formula apart to see how it works...could you please explain what it does?
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
here is array solution for that
the text in a1 cell> =RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(" ",TRIM(A1),ROW($1:$50)),0))) CSE
Hi FDibbins,
I learned this from DonkeyOte. You take a string with spaces in it. You expand each space by the length of the entire string. Then you take the right-most part of this expanded string and trim it. Trim will remove all those extra spaces. You can improve the idea to get the second third or forth word in a string using the same idea.
BTW - It took me a few tries to understand what DO had done before I owned the trick myself.
See the generalized formula in the attached...
Last edited by MarvinP; 01-24-2013 at 05:55 PM.
Thanks for the explanation, I think I had just waded through it myself....
(I know you said this, but just getting my head around it)
1. it expands the string by increasing each space to the length of the string
2. This means that the last word will definitely be contained within the the right-most length of the original string - with a bunch of extra spaces
3. trim the extra spaces, and...bingo
Nice example, too, thanks a bunch
Thanks for the help guys it save me quite some time
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks