Extracting Data
 

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

Extracting Data
 

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?

Extracting Data
 

=IF(AND(ISNUMBER(--LEFT(A2)),ISNUMBER(FIND(" ",A2))),LEFT(A2,FIND("
",A2)-1),"") perhaps?
--
David Biddulph

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

Extracting Data
 

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?

Extracting Data
 

This spits back the samething, the number?

=IF(AND(ISNUMBER(--LEFT(I2)),ISNUMBER(FIND(" ",I2))),LEFT(I2,FIND("
",I2)-1),"")


=IF(AND(ISNUMBER(--LEFT(A2)),ISNUMBER(FIND(" ",A2))),LEFT(A2,FIND("
",A2)-1),"") perhaps?
--
David Biddulph

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

Extracting Data
 

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?

Extracting Data
 

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?

Extracting Data
 

Sorry, the other part came to me once I started to try the first suggestion.

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?

Extracting Data
 

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?

Extracting Data
 

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?