Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
parsing out an undelimited address and city
I am trying to write programing to parse out a data file to seperate
the address and the city. Examples below: 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON The results I want are below: Address City 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON I have tried text to columns with delimited by spaces, but the problem is you don't know how many spaces there are? Any help? Thanks KTG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
parsing out an undelimited address and city
With all the variation of addresses, the only reliable way that I know is to do
it manually. Good luck. wrote: I am trying to write programing to parse out a data file to seperate the address and the city. Examples below: 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON The results I want are below: Address City 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON I have tried text to columns with delimited by spaces, but the problem is you don't know how many spaces there are? Any help? Thanks KTG -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
parsing out an undelimited address and city
On Feb 1, 4:25 pm, Dave Peterson wrote:
With all the variation of addresses, the only reliable way that I know is to do it manually. Good luck. wrote: I am trying to write programing to parse out a data file to seperate the address and the city. Examples below: 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON The results I want are below: Address City 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON I have tried text to columns with delimited by spaces, but the problem is you don't know how many spaces there are? Any help? Thanks KTG -- Dave Peterson- Hide quoted text - - Show quoted text - Dave - thank you for your answer. I have always found your answers to be helpful. McRitchie - suggests replacing common road extensions with " St ", with " St, " then delimit by the comma - this is great except for things like 1215 S Main St St John MN .... i guess I could check if there where more than 3 columns of data and have a manual look up of that. ..... Your thoughts. Thanks, Kenlyn |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
parsing out an undelimited address and city
On Feb 2, 9:08 am, Dave Peterson wrote:
You may be able to do stuff based on the number of strings in the cell--but I still think it's gonna be a long and tedious effort. Say your data is in C1:Cxxx And then put this in B1: =trim(c1) And put this in A1: =LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1 Now you can sort your data by column A. Maybe you'll be able to see a pattern--select the range with 3 elements and extract that data. Then look at the items with 4, then 5. You may be able to use some of David McRitchie's suggestions, too. It ain't gonna be easy. wrote: On Feb 1, 4:25 pm, Dave Peterson wrote: With all the variation of addresses, the only reliable way that I know is to do it manually. Good luck. wrote: I am trying to write programing to parse out a data file to seperate the address and the city. Examples below: 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON The results I want are below: Address City 1149 N CARRIER PKY APT A44 GARDEN PRAIRIE 306 ROYALTY AVE WALHALLA 40 DANAS CT APT 1 WESTBRIDE 4139 W HAVEN AVE MORTON I have tried text to columns with delimited by spaces, but the problem is you don't know how many spaces there are? Any help? Thanks KTG -- Dave Peterson- Hide quoted text - - Show quoted text - Dave - thank you for your answer. I have always found your answers to be helpful. McRitchie - suggests replacing common road extensions with " St ", with " St, " then delimit by the comma - this is great except for things like 1215 S Main St St John MN .... i guess I could check if there where more than 3 columns of data and have a manual look up of that. ..... Your thoughts. Thanks, Kenlyn -- Dave Peterson- Hide quoted text - - Show quoted text - Dave: Thanks. My goal is the FIX the easy ones and have a manual human eye on the difficult ones. This should help. We have placed a request for the client to export the data in a better format - but they don't think they can. Have a great weekend. You bring me value. Kenlyn |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
parsing out an undelimited address and city
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parsing out an undelimited address and city | Excel Discussion (Misc queries) | |||
How do I manipulate a string to collect the address from it? | Excel Discussion (Misc queries) | |||
Splitting comma separated lines of an address for mail merge. | Excel Discussion (Misc queries) | |||
Split Address Correction | New Users to Excel | |||
Address labels to columns | Excel Discussion (Misc queries) |