Parse function

[Guest]

Hello, I have a field that contains
"Housenumber+Direction+Street+StreetType+City+Zip+State. I would like to
break this data down into seperate fields. Not every entry neccesarily has
all the elements and the elements are broken down by spaces instead of
commas. Is there a way to do what I am looking for.

 

[Rick B]

Probably not since some addresses do not have all the elements. Also,
street names could have more than one word. Twin Lakes Drive, for example.

It would be almost impossible to build a reliable function that could do
this. You might build something that gets the most common items (maybe find
all the addresses with only four spaces in them and assume it is
Housenumber, Street, City, State, and Zip). You could review the results in
a select query, then apply them by turning that query into an Update query.
(Make backups first).

Next, you could take the remaining addresses and manually divide them up.
You might even try pulling just the ones with five spaces and breaking them
out using a query. Chances are pretty good that you can get more than half
of them right.

I'm curious though. Where is suite or apartment number? Where is
post-directional indicator? I'd expect to see something more like...
Housenumber+Direction+Street+StreetType+PostDirectional+Apartment+City+Zip+State

(123 Avenue M South, apartment 123...)

 

[Guest]

That's what i was afraid of. Actually, i forgot to mention those elements
(i.e. PostDirection). They do exists. Does that fact help matters at all?

 

[Allen Browne]

This kind of thing is not simple to do. You will probably need to attack it
a bit at a time, until you have all your fields populated.

The first stage might to be get some system to the data. Use an Update query
to replace commas in this FullAddress field with spaces, i.e.:
Replace([FullAddress], ",", " ")
Then another Update query to get rid of double-spaces, i.e. update this
FullAddress field to:
Replace([FullAddress], " ", " ")

You can now determine how many spaces there are in the field with:
SpaceCount: Len([FullAddress) - Len(Replace([FullAddress], " ", "")

Now you know how many components there are to the address. Look at the data,
and decide whether to attack from the front or the back, i.e. if most
addresses have a Housenumber+Direction+Street you can go forwards, but if
most end with StreetType+City+Zip+State, it might be easier to come in
backwards.

Use a function like this to parse a word from the FullAddress field:
http://allenbrowne.com/func-10.html
Populate the true fields with the data, and use Left(), Mid(), Right()
Len(), and Instr() to chop that data off the FullAddress. Keep going until
you have the fields populated correctly.

(Update is on the Query menu in query design.)