Help with Address Formula
 

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in
cell A2: Castleton Parkway

TIA

Help with Address Formula
 

One way:

=MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))



Steve M wrote:

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in
cell A2: Castleton Parkway

TIA

--

Dave Peterson

Help with Address Formula
 

Try this:
=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))


"Steve M" wrote:

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in
cell A2: Castleton Parkway

TIA

Help with Address Formula
 

When I use this formula it returns Cast. I might mention also that there
would usually be a number in front of the street address (e.g. 125 Castleton
Parkway Blvd--in such a case I would want the result of the formula to be
125 Castleton Parkway).
"Dave Peterson" wrote in message
...
One way:

=MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))



Steve M wrote:

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return
in
cell A2: Castleton Parkway

TIA

--

Dave Peterson

Help with Address Formula
 

Just a minor modification of Dave's formula:

=MID(A1,1,FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

HTH
Kostis Vezerides

Steve M wrote:
When I use this formula it returns Cast. I might mention also that there
would usually be a number in front of the street address (e.g. 125 Castleton
Parkway Blvd--in such a case I would want the result of the formula to be
125 Castleton Parkway).
"Dave Peterson" wrote in message
...
One way:

=MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))



Steve M wrote:

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return
in
cell A2: Castleton Parkway

TIA

--

Dave Peterson

Help with Address Formula
 

Thanks for the correction...

And just because of the unfortunate line wrap:

=MID(A1,1,FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)



vezerid wrote:

Just a minor modification of Dave's formula:

=MID(A1,1,FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

HTH
Kostis Vezerides

Steve M wrote:
When I use this formula it returns Cast. I might mention also that there
would usually be a number in front of the street address (e.g. 125 Castleton
Parkway Blvd--in such a case I would want the result of the formula to be
125 Castleton Parkway).
"Dave Peterson" wrote in message
...
One way:

=MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)
-LEN(SUBSTITUTE(A1," ","")))))



Steve M wrote:

I am looking for a formula that will return all words except for the last
word.
Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return
in
cell A2: Castleton Parkway

TIA

--

Dave Peterson

--

Dave Peterson