Extracting Data

[Curtis Stevens]

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

 

[Kevin B]

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)

 

[Curtis Stevens]

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.

 

[Curtis Stevens]

This spits back the samething, the number?

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

 

[John C]

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))

 

[Curtis Stevens]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[John C]

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.