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

Bondi supplied this formula :
=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

but I cannot get it to work--I get a #VALUE! result whether or not I use it
as an array.


TIA

On Wed, 29 Nov 2006 09:10:47 -0500, "Steve M"
wrote:

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

Bondi supplied this formula :
=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

but I cannot get it to work--I get a #VALUE! result whether or not I use it
as an array.


TIA



If you are using the formula exactly as you pasted it in here, there is a line
feed in the "wrapped" first SUBSTITUTE function, instead of a <space.

That will cause the behavior you are seeing.

This should correct that:

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

Or you can do it manually yourself.


--ron

See if this will work:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,LEFT(A1,LEN(A1)-(LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

If there is only one word/num in the address for some reason, the above
formula will return that word/num. If that is not an issue, get rid of the
IF as such:

=LEFT(A1,LEN(A1)-(LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

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

Bondi supplied this formula :
=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

but I cannot get it to work--I get a #VALUE! result whether or not I use
it as an array.


TIA

Hi Steve,

You could try this more concise array formula.
=MID(C13,1,MAX((MID(C13,COLUMN(1:1),1)=" ")*COLUMN(1:1))-1)
However, it has the problem that it is an array formula and hence slower
than non array formulas.

"Steve M" wrote:

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

Bondi supplied this formula :
=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))

but I cannot get it to work--I get a #VALUE! result whether or not I use it
as an array.


TIA