Pull last word from a text string in Excel

[nmp]

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

I have a sheet 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!

 

[Ron Coderre]

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

 

[Ron Rosenfeld]

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

I have a sheet 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!

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)


--ron

 

[nmp]

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]

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]

That worked!

Thank you!

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)


--ron

 

[Ron Rosenfeld]

That worked!

Thank you!

You're welcome.

Note that if there are no spaces in the string, the formula will return an
error. If that needs to be avoided, use this instead:

=IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,MID(
A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(
A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))


--ron

 

[Biff]

Here's another one:

=RIGHT(A5,LEN(" "&A5)-LOOKUP(LEN(" "&A5),FIND(" ","
"&A5,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A5),1)))))

"Error trap" built-in.

Biff

 

[Biff]

Oh, I see Ron C has already posted pretty much the same thing.

Biff