String manipulation, remove suburb from address

Hello everyone,
i have entries like "76 GROSVENOR STREET ST KILDA EAST"
and "8 YAWLA STREET MCKINNON" in one column of a database.
my aim is to seperate the suburb from this address string, so it is like "8 YAWLA STREET" and "MCKINNON", where the suburb can be one two or three words. I have all the suburb names in another column and table, so i could match the address string against the suburb list. i have all this data in an access database, and an excel spreadsheet aswell, and im looking for a solution in any way possible.

Thanks for your help.

Are you using Script in Access? Or just the queries?

If you're using script inside a module, you have lots of choices depending on the format of ALL fields:

If all fields include "STREET" after the street address and proceeding the suburb (with nothing following the suburb):

intStreet = instr(strAddress, "STREET")
intLength = Len(strAddress)
intSuburbLength = intLength - intStreet)
intSuburb = mid(strAddress, intStreet + 7, intSuburbLength)

(give or take an intLength)

Or better yet, since you have a database of ALL suburb Names and they will match exactly to the suburb names in the table:

Fil strSuburb(x) with all the possible suburbs
Open your table of address

While not rs.eof
for x = 0 to intNumberOfSuburbs
if instr(rs.Address, strSuburb(x)) > 0 then
rs.Suburb = strSuburb
else
rs.Suburb = "Suburb Not Found"
endif
Next
Wend

...But there is a posibility that "York" would get confused with East York, etc....

so, use a combination of both?

If you're only using querries... you would have to use something similiar in access, but im no access guy...