Oh, I agree with error trapping. I was more referring to the fact that your
formula 'got the same result' as Kevin B's formula did, when in fact you were
responding to the same question as Kevin B, and not to the OP follow up to
Kevin B.
--
John C


"David Biddulph" wrote:

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?