Pull last word from a text string in Excel

N

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!
 
R

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
 
R

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
 
N

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!
 
R

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
 
R

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top