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.