RE: Pull last word from a text string in Excel

You wouldn't need to alter the INDEX portions of the formula.

For a string in E2 that may or may not contain a space

F2: =IF(COUNTIF(E2,"* *"),RIGHT(E2,LEN(E2)-LOOKUP(LEN(E2),FIND("
",E2,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(E2),1))))),E2)

Actually, Ron Rosenfelds formula may be easier to decipher. If there may be
no spaces in the string, just alter it to this:
F2: =IF(COUNTIF(E2,"* *"),MID(E2,FIND(CHAR(1),SUBSTITUTE(E2,"
",CHAR(1),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))+1,255),E2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"nmp" wrote:

Ron,

I'm not sure what to change on the INDEX functions to match my data. My
data starts in cell E2 and I have 1586 rows, so I want to be able to copy the
formula all the way down. I'll start the formula in F2.

There are spaces in each cell of data I want to extract the last word from.

Thanks!

"Ron Coderre" wrote:

Try this:

For text in A1

This formula returns the word after the last space in A1
B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("
",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)))))

Note: if there are no spaces the formula returns #N/A.

This formula allows for no-spaces contents
B1: =IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("
",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"nmp" wrote:

Is there a way to pull just the last word from a text string in Excel?

I have a *** with a column of text descriptions that are various lengths
and the last words in each cell are various lengths. Basically I want
everything to the right of the last space.

Thanks!
.