Pull last word from a text string in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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!
Click to expand...


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


--ron
 
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!
 
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
 
That worked!

Thank you!
Click to expand...

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
 
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
 
Guest said:
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!
Click to expand...


To Pull last word from a text string you may try:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
--------------------------------
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
 
Back
Top