C
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?
address & put it into a separate field/box?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
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)
following function will do the trick. Change cell A1 accordingly.:
=LEFT(A1,FIND(" ",A1,1)-1)
D
David Biddulph
=IF(AND(ISNUMBER(--LEFT(A2)),ISNUMBER(FIND(" ",A2))),LEFT(A2,FIND("
",A2)-1),"") perhaps?
",A2)-1),"") perhaps?
C
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.
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.
C
Curtis Stevens
This spits back the samething, the number?
=IF(AND(ISNUMBER(--LEFT(I2)),ISNUMBER(FIND(" ",I2))),LEFT(I2,FIND("
",I2)-1),"")
=IF(AND(ISNUMBER(--LEFT(I2)),ISNUMBER(FIND(" ",I2))),LEFT(I2,FIND("
",I2)-1),"")
J
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))
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))
D
David Biddulph
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.
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.
C
Curtis Stevens
Sorry, the other part came to me once I started to try the first suggestion.
R
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
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
J
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.
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.