Jewtus Posted December 15, 2014 Share Posted December 15, 2014 I'm struggling to find a good way of doing this. I have a physical address that has no delimiters in it and I'm trying to make it output a formatted version of the original string. For example: 123 Happy Lane New York City NY 100219809 I've been able to format it a bit: $StringLength=StringLen($Address) $Part=StringRight($Address,11) $survivorState=StringLeft($Part,2) $survivorZip=StringRight($Part,9) $Formedaddress=StringLeft($Address,$StringLength-12)&","&$State&","&$zip but I cannot figure out a good way of splitting the address from the city. Anyone have any clever Ideas? Link to comment Share on other sites More sharing options...
kylomas Posted December 15, 2014 Share Posted December 15, 2014 (edited) Jewtus, Addresses come in a bunch of formats, as you are probably finding out. Ignoring the fact that there could be routing lines, ATTN, Suite, CO, etc and dealing with addresses in the format that you presented this is how I would start: Get the zip code (last 9 numbers) and format them as 99999-9999. Take the zip code out of the target string. Search for the city using a list like this. Once found, blank it out from the target string. Now you are left with the street address on the left and the state on the right with several intervening blanks. Of course there are problems: What to do if the city is not found Malformed strings City and State have same name But you may be able to code around them. On the other hand, if this is a one-off and there are only a couple of hundred of them, do them manually. Good Luck, kylomas edit: Try Google for several links for downloading city, state and county names Edited December 15, 2014 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Solution iamtheky Posted December 15, 2014 Solution Share Posted December 15, 2014 (edited) #include <Array.au3> $string = "123 Happy Lane New York City NY 100219809" $aStr = stringsplit($string, " ") $aStr[$aStr[0] - 2] = $aStr[$aStr[0] - 2] & "," If stringlen($aStr[$aStr[0]]) = 9 Then If StringInStr($aStr[$aStr[0]] , "-") = 0 Then $aStr[$aStr[0]] = stringleft($aStr[$aStr[0]] , 5) & "-" & stringright($aStr[$aStr[0]] , 4) Endif for $i = 1 to $aStr[0] If $aStr[$i] = "Lane" Then $aStr[$i] = $aStr[$i] & "," Next $sFrmt = _ArrayToString ($aStr , " " , 1) msgbox(0 , '' , $sFrmt) Some will have set positions, but you will have to account for all the ways people write street names Court, Crt, Ct., etc... in the loop, as that will be the only way I can think of to discern where the address stops and city begins. You can assume the zip will be last, the state second to last. edit: tried to wrap the 9 digit in an if statement and boned the formatting of the post...but at least its not all ugly on 5 digit zips now. Edited December 15, 2014 by boththose Jewtus 1 ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
mikell Posted December 15, 2014 Share Posted December 15, 2014 I suggest googling "parse us address" to see the magnitude of the problem Link to comment Share on other sites More sharing options...
kylomas Posted December 15, 2014 Share Posted December 15, 2014 (edited) Another link for U.S. cities and states... This list makes it even easier... Get city, state and zip...what's left is the street address. The problem I see is if you have addresses like this: 123 E My ST ATTN: Mail Room C/O Me West Allis, Wi 53214 which would look like "123 E My ST ATTN: Mail Room C/O ME West Allis Wi 53214" in your format Edited December 15, 2014 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Jewtus Posted December 15, 2014 Author Share Posted December 15, 2014 Ya I've been googling for a while trying to find the best way of doing this. It does look like the script that both came up with works pretty well (after I added some alterations) Link to comment Share on other sites More sharing options...
kylomas Posted December 15, 2014 Share Posted December 15, 2014 (edited) Delivery locations can be in so many different forms I don't see how that technique can work...but if it works for you, I'm out'a here Edited December 15, 2014 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
iamtheky Posted December 15, 2014 Share Posted December 15, 2014 I see ATTN: xxx at the top of the address usually in place of the Name, not midstroke. So adding OR $aStr[$i] = "ST" doesnt make it the cleanest as you would probably like a delimiter between ME and the city, but it is no less legible than it was to begin with. Edge Cases are indeed edgy. ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
kylomas Posted December 15, 2014 Share Posted December 15, 2014 Agreed, that's why I opted for remove all known and what's left is the delivery location (123 My ST)...regardless, the OP seems satisfied with your solution Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
iamtheky Posted December 15, 2014 Share Posted December 15, 2014 I started down your path, but then opted to half-ass it. I also want to see the OPs alterations (or a badass regex), there are soooo many variances I would like to see the result of one that catches anything over 50% of them. ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
kylomas Posted December 17, 2014 Share Posted December 17, 2014 Jewtus, F.Y.I. Followup...There is an open source project called Open Street Map (OSM). They have street names, by area, current to within hours...just google OSM... kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Jewtus Posted December 22, 2014 Author Share Posted December 22, 2014 Jewtus, F.Y.I. Followup...There is an open source project called Open Street Map (OSM). They have street names, by area, current to within hours...just google OSM... kylomas I just took a look... This does look promising, but unfortunately, I can't run my data through any third party sources. Thanks though. Link to comment Share on other sites More sharing options...
kylomas Posted December 22, 2014 Share Posted December 22, 2014 Yes, I understand. The reason that I posted this was if you get to the point where you need to verify addresses or address formats you can download every address in the U.S. at this site. Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Jewtus Posted December 22, 2014 Author Share Posted December 22, 2014 Oh snap... I had not though about doing that... Thanks!! Boss is out this week for the holidays and left me no work so maybe I'll work on a UDF for address transforming. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now