Help With Address Function

S

Steve M

Steve M skrev:
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
 
R

Ron Rosenfeld

Steve M skrev:

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
Click to expand...


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
 
B

Bob Davison

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," ",""))))))
 
Top