G

#### Guest

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!

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

G

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!

G

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

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

G

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!

G

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

G

Thank you!

Ron Rosenfeld said:=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

--ron

R

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

=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

B

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

Biff

Biff

- Joined
- Jul 2, 2016

- Messages
- 1

- Reaction score
- 0

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!

To Pull last word from a text string you may try:

Michael (Micky) Avidan

ISRAEL