Steve

Works for me with the two examples you provided.

Did you type in or copy Peo's formula correctly?

Is your data exactly as your examples indicate?

Do you have data in A1?

I get the #VALUE! error if no data to parse.


Gord Dibben MS Excel MVP


On Fri, 20 Jan 2006 16:04:39 -0500, "Steve Madden"
wrote:

I tried this but it returned #VALUE! instead of the last word.
"Peo Sjoblom" wrote in message
...
One way

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)


--

Regards,

Peo Sjoblom


"Steve Madden" wrote in message
news:Yjbxf.52522$ih5.7457@dukeread11...
I work with street names. I know how to find the suffix or last word in
a
cell, but I want to find all EXCEPT the last word in a cell. For
example,
if
cell A2 contained the Name Orchard Trail Rd, I would like a formula that
would return Orchard Trail. If the street name was Duke of Gloucester
Drive, I would like the formula to return Duke of Gloucester.

TIA