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 nospaces 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):
Relevant Pages
