Help Parsing Free Form Addresses into multiple fields

[Bronte1226]

I have a database with 3 address fields which vary very widely in the data each has in it. I need to parse this data out into separate fields, and perhaps concatenate them back into proper formats afterward. The goal is to clean up the addresses into a structured format. Prior to this, the information was entered in a free form matter that makes this process nothing short of a nightmare.

For instance
Over the three address fields, the address1 field might have a street address, or it might have an attention to name, or it might have po box with city and state, etc. This continues on to the address2 and address3 fields.

My thinking was to parse out by spaces in each field. That way everything is separated and can be put back together by markes such as PO and Box.

My question!
What is the best way to parse out this information?
I used this to move out the first part of address1 Left$([Add1],InStr(1,[Add1]," ")-1)

Worked great. Now, how do I either delete the parsed out information or move on to the next space in the field?

In any given field there might be 2 to 6 spaces in an address. So one field needs to be parsed out into 6 fields!

 

[jedraw]

Judicious use of

Left(), Right(), Mid(), Instr() and InstrRev()

and lots of trial and error.

 

[SkipVought]

First you have to analyze this instance of data using a variety of parsing techniques, as each instance has it own unique set of challenges.

Siphon off the groups of data that have the same parsing logic, leaving more difficult data.

It can be very tedious and labor intensive process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[missinglinq]

Years ago, when I was in school, I worked for a temp agency doing data entry. The agency had a contract with one of the biggest banks in the country, doing this very thing, in order to get the address data from a customer database, of a smaller bank they'd gobbled up, to comply with their own data systems. I had occasion to talk with one of their IT people who was supervising this effort. They'd had a team of six experienced programmers assigned to do the project of doing this through code, and after four frustrating months, finally gave up on the idea and decided that re-entering the data was the only feasible approach.

The situation when entire names (first, last, prefixes, suffixes and titles) are entered into a single field and must later be parsed into the individual components is a super mare's nest, but a walk-in-the-park when compared to parsing addresses! There are so very many variations/permutations, where addresses are involved, that no matter how many possibilities you plan on, no matter how clever you are, there will always be many more you didn't think of, and in the end, they will all have to be eyeballed by human beans, in order to insure correctness.

GOOD LUCK TO YOU!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!