Parsing Complex Addresses

[dgregg1035]

I need to sort an address listing by street name. My address field contains variable length street numbers and street name, as well as direction (i.e. N,S,E,W) &/or intersections (i.e. Main St/Johnson Ave). I have not found a formula that addresses the intersection and direction issues. Any help is greatly appreciated - I've been stumped for two days.

Example of data:

123 Main St
12345 Redding Ave
1234 S 1st St
123 N Arrow Ave
Capitol Ave/Donner Rd

Using the above address examples, I need to be able to group using street names of:

Main
Redding
1st
Arrow
Capitol

 

[Turkbear]

Hi,
In theory you could develop a formula ( or series of formulae) that:

First step:
Find first space ( Using InStr)
Then
Check next 2 positions to see if more than 1 char is there
If Only 1 ( that is, position of 1st space + 2 is blank) then assume N,S,E or W is there so:
 Extract the characters ( Using a combo of MID and Instr functions) from the blank's position found in previous step + 1 
Then
Use the Left and Instr functions extract the chars between the start and either the next blank or the '/' character.
If in checking the chars after the first space, more than 1 is found, go directly to the above step, leaving out the step involving N,S,E,W chars.

There may be a simpler way, ( using Array functions, maybe, since the text in the field can be converted to an array using the Split function) but this shoud give you some idea of how to proceed.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dgregg1035]

Thank you for your response. I'm not very advanced with writing code, but I'll certainly give it a try. At least now I have some direction. Thanks!

 

[Erbium]

If you use address scrubber databases you can standardize them but more importantly you can append lat lon info at street level. this would let you group addresses nearby.

the software is expensive but any bulk mailing company will typically scrub the files for you and return them in identical format with location info appended for a fee