RE: Pull last word from a text string in Excel



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



Relevant Pages

  • Re: Please explain the one string
    ... So we read the input files name and temp in a loop that much is clear from ... The formatting of the output is done is done in the first string. ... we are still in the file 'temp' but the line does not start with ALTER TABLE ... in a small script where you add comments and make sure that the file names ...
    (perl.beginners)
  • Re: OT: Atheism. (Was Re: Alter a string in multiple files?)
    ... Subject: OT: Atheism. ... Alter a string in multiple files?) ...
    (comp.sys.acorn.apps)
  • Re: Nochmal Vererbung
    ... public Person(string name, int alter, string addresse) ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)
  • Re: plans/ schematic for a guitar/ instrument string sustaining device??
    ... >> properties of the string, like with one of those bend bar things. ... > tried to alter the device frequency that it would alter the strings? ... > afraid the string will break under too much tension. ... > Is this any relative to the ring modulator? ...
    (sci.electronics.design)