Text with Spaces

[Trigger99]

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.

[scott.s.fower]

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

[MarvinP]

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.

[FDibbins]

Hi Marvin, I have been trying to pull your formula apart to see how it works...could you please explain what it does?

[e.isayev]

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

[MarvinP]

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...

[FDibbins]

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

[Trigger99]

Thanks for the help guys it save me quite some time