I would use these...

B1: =IF(AND(ISNUMBER(--LEFT(A2)),ISNUMBER(FIND(" ",A2))),LEFT(A2,FIND("
",A2)-1),"")

C1: =TRIM(SUBSTITUTE(A2,B2,"",1))

The first is what David posted. Together, these will work when there is a
lead number, for example...

123 Main Street

AND when there isn't one, for example on a rural route address like this...

RR 12 Box 345

Rick

"Curtis Stevens" wrote in message
...
What would be the formula to separate the street portion then, so I can
have
both in separate boxes, this strips it & puts the numbers in a new field,
but
the original field still has the number & street name & not just street
name.


Assuming that he Number portion of the address is followed by a space,
the
following function will do the trick. Change cell A1 accordingly.:

=LEFT(A1,FIND(" ",A1,1)-1)
--
Kevin Backmann


"Curtis Stevens" wrote:

If you have addresses in a field, how can you extra the number portion
of the
address & put it into a separate field/box?