# RE: Pull last word from a text string in Excel

*From*: Ron Coderre <ronREMOVETHIScoderre@xxxxxxxxxxx>*Date*: Mon, 25 Sep 2006 12:53:02 -0700

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

- Prev by Date:
**RE: calculate the sum of characters in a cell** - Next by Date:
**Re: Need big help with look up....is this possible** - Previous by thread:
**Re: Pull last word from a text string in Excel** - Next by thread:
**Re: Pull last word from a text string in Excel** - Index(es):