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

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

Try this:
For A1 containing the text
B1: =LEFT(A1,LEN(A1)-MATCH("
",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0))
note: commit that array formula by holding down [Ctrl]+[Shift] and press
[Enter]

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Steve Madden" wrote:

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

Here's a non-array formula that also works:

B1: =LEFT(A1,SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)


***********
Regards,
Ron

XL2002, WinXP-Pro


"Steve Madden" wrote:

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

Steve Madden wrote...
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.

If you have the last word of a string, which I'll denote LW, then the
portion to the left of it would just be

=LEFT(s,LEN(s)-LEN(LW)-1)

Without the last word, you could define a name like seq referring to a
formula like

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

which would evaluate to an array of sequential integers, then use a
formula like

=LEFT(s,LOOKUP(2,1/(MID(s,seq,1)=" "),seq)-1)

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

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

I also got a #VALUE error because when copied and pasted the line wrap
caused the space in the second SUBSTITUTE to be lost. Put the space in the
required place and it works.

--
HTH

Sandy

with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

That was it--thank you. I will remove from group in a couple of days.
"Sandy Mann" wrote in message
...
I also got a #VALUE error because when copied and pasted the line wrap
caused the space in the second SUBSTITUTE to be lost. Put the space in
the
required place and it works.

--
HTH

Sandy

with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

=left(a1,lookup(len(a1),find(" ",a1,row($a$1:index($a:$a,len(a1)))))-1)


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=500266