Help With Address Function

[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

 

[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

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

 

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