Convert Text String Address To Separated Text

Earl C Phillips

Volunteer Programmer
Local time
Today, 07:57
Joined
Jul 18, 2008
Messages
40
I have an address box containing freeform addresses, and the client wants to change it to separate text boxes for number, direction, street name, type of street, apartment number, etc.

Does anyone know of a module that will make the conversion, or do I have to write it myself? This is in a Volunteer Scheduling application at the local food bank that wants to eliminate duplicate records for volunteers where clerks have entered the volunteer multiple times because they were too distracted to avoid duplicate entries.
 
Given the potential for inconstancy in the data entry of addresses you are going to have your work cut out for you.

Start by having a look at the various Text Manipulation function available here. This link may give you a start point, also try searching this forum on the subject of splitting text (or similar)
 
You're probably going to have roll your own solution here. Could be tricky depending on how inconsistent the current data is. You would likely need a combination of different string functions (Left, Mid, InStr, etc.) using perhaps the spaces as the delimiter. If you can post some sample data we may be able to offer more specific advice.
 
the big problem is identifying the town, county, and maybe even post code.
(you are in US, but similar problems)

as a start, could you strip out the zipcode, and split the rest into indivdual lines. that might get you started.

just not an easy task at all, to automate, though.
 
Once you get through this, you will NEVER, EVER allow mushed data to be stored in a single field. Once the parts are concatenated together, it is almost impossible to separate them again with 100% accuracy unless a delimter was used to separate each part.

Start your parsing by moving from right to left so you extract the zip first, then the state, then the city. The rest is the street address. You can try to separate that also but it might be easier to just remove punctuation characters and extra spaces to sort of normalize it. Hopefully, if you have to parse the street address, you live in a city where there is a strong naming convention so you can easily identify pre- and post- directional elements.
 
hsaving separate fields for each bit can also be ambiguous as well though.

to be honest, most of the time, i tend to use a 255 char text field, for addresses, with a separate postcode/zipcode.
 
Thanks for all the advice. I can't say I am looking forward to this, but this is why this field is so interesting: a new puzzle every day.
 

Users who are viewing this thread

Back
Top Bottom