John is quite right that you need to be complete with your question.
The reason that my formula was longer than John's is that I tried to do some
error trapping for cases with no number, or no spaces in the string. You
can adjust the formula to suit your own requirements.
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
Remember, when you post a question, you should post your entire question,
and
not have constant follow-ups. Also, when reading responses, you should be
aware that some people are answering the same question, and not
necessarily
reading (or even seeing) your follow-up questions.
Assuming your data is in the form of #### Street name, and this is in A1:
B1: =LEFT(A1,FIND(" ",A1,1)-1)
C1: =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
--
John C


"Curtis Stevens" wrote:

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?