split address field

[Guest]

I am working with a large database imported from excel. The address field
may contain a street number followed by a street name followed by an
apartment number. Some addresses have no street number, some have no
apartment number. I need to compile a walking list from this data so I need
to extract the street name from the "address" and then list the house number
and apartment number where applicable in separate fields. The street name may
be any number of separate words.
For example: 501 West Vernal Pike North apt. 3 or South SR 45......There are
no separators between the numbers and words, just spaces.
All help will be appreciated!

 

[Allen Browne]

HI Bibi

There's really no reliable way to automate this process. The best you can
hope for is to handle the most common options, and then do the others
manually.

Presumably you have a table with a field that has this combined Address, and
you want to split it into different fields. You can use an Update query to
populate the other fields.

First step is to create the query.
Drag the combined Address field into the grid.
In the Criteria row under this field, enter:
Is Not Null

Now, to have a shot at the street number.
In a fresh column in the Field row, enter:
Val([Address])
In the Criteria row under this, enter:

0

Check that this is giving sensible output.
Change the query to an Update query (Update on Query menu.)
Repeat the Val([Address]) in the Update row under the Streetnumber field.

Other functions you will need include: Left(), Mid(), Right(), Len(),
Instr(), InstrRev(), Trim(). For example, the last word in the address is
most likely "Street" or similar, and you can parse that word with:
Trim(Mid([Address], InstrRev([Address], " "))
Then the middle part would be:
Trim(Mid([Address], Len(Streetnumber) + 1, Len([Address] -
Len([Streetnumber]) - Len([StreetType]))

Ultimately, it may be an exercise in frustration, since it is not always
possible to parse addresses like that. There are just too many variations,
such as:
- First Floor, Fred Myers Building, Cnr Walter and Stein Steets
- Lot 64, Swan Road
- PO Box 99
- Unit 17B, 7A Main St
- Care Of Marth Jones, 1 Bruce St
- Room 9, Ward C, Royal Albert Hospital
and that's before we start on addresses in other countries.