Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data | Excel Worksheet Functions | |||
Extracting Data | Excel Discussion (Misc queries) | |||
Need help extracting data.... | Excel Worksheet Functions | |||
Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
extracting data | Excel Worksheet Functions |