Splitting up address

[Simon]

i have a lot of address in the following format

2, Church Street, Fforestfach, Swansea, West Glamorgan SA8 8UD


Is there a way i can copy this into a field and then press a button so
it splits up the addres into the fields i currenly have in my database

Address 1
Address 2
Address3
Address 4
AddressCity
AddressPostCOde

This would save a lot of time insead of coying and pasting each line
of address

Thanks

 

[BruceM]

This may help get you started:
http://www.mvps.org/access/strings/str0001.htm

If all of the addresses are separated by commas as they are in the example,
it will be very similar to extracting the last name as shown in the example
in the link, except that you will have to do it several times. If the
postal code is always the same number of digits you can extract that readily
enough using the Right function.

One approach would be to base a query on the table, then do something like
this in a blank column in the query design grid:
StreetNumber: Left$([LongAddress],InStr(1,[LongAddress],",")-1)
This should extract the street number. Note that it is a single character
with the example you have given. Now, if the street number is always
followed by a comma and a space, you maybe would do something like this
(watch out for line wrapping; this should be all on one line):
RestOfAddress: Right([LongAddress],Len(]LongAddress]) -
(Len(Left$([LongAddress],InStr(1,[LongAddressField],",")-1)) + 2)

Remember that Left$([LongAddressField],InStr(1,[LongAddressField],",")-1)
results in a single character (a "2") in your example. Adding 2 to that
gives you three (a three-character string). The length of LongAddress is
about 62. Subtracting three from that leaves 59. Rest of Address, then,
evaluates to:
Right([LongAddress],59)
Which is to say the 59 rightmost characters in LongAddress, which results
in:
Church Street, Fforestfach, Swansea, West Glamorgan SA8 8UD

If the postal code is always seven characters (including the space) and is
always preceded by a space, you can extract the rightmost seven characters
from the field into a PostalCode field in similar fashion as described
above.

You can run this query as a make-table query to create the new fields
StreetNumber, PostalCode, and RestOfAddress. In similar fashion you can
extract the street name, etc. Study the functions in the link, and approach
it incrementally. You could probably do this in a single query by piling
the expressions on top of one another, but I think it would be a lot easier
to make new tables as you go.

 

[Guest]

Concatenate the data in your query like this --
ADDR: [Address 1] & IIF([Address 2] Is NULL, NULL, ", " & ([Address 2]) &
IIF([Address 3] Is NULL, NULL, ", " & ([Address 3]) & IIF([Address 4] Is
NULL, NULL, ", " & ([Address 4]) & ", " & [AddressCity] & " " &
[AddressPostCOde]

 

[BruceM]

I think the idea was to break apart the long field, not to assemble it from
shorter fields. As I understand it, some records are split up into
Address1, etc., while others are in the long format. The OP wants to break
the long address field into its components.

Concatenate the data in your query like this --
ADDR: [Address 1] & IIF([Address 2] Is NULL, NULL, ", " & ([Address 2])
&
IIF([Address 3] Is NULL, NULL, ", " & ([Address 3]) & IIF([Address 4] Is
NULL, NULL, ", " & ([Address 4]) & ", " & [AddressCity] & " " &
[AddressPostCOde]

i have a lot of address in the following format

2, Church Street, Fforestfach, Swansea, West Glamorgan SA8 8UD


Is there a way i can copy this into a field and then press a button so
it splits up the addres into the fields i currenly have in my database

Address 1
Address 2
Address3
Address 4
AddressCity
AddressPostCOde

This would save a lot of time insead of coying and pasting each line
of address

Thanks

 

[John Nurick]

Hi Simon,

This is quite easy - but only if *every* address is formatted exactly
the same as your example
StreetNumber <comma>
StreetName <comma>
Locality<comma>
PostTown <comma>
RedundantAndProbablyObsoleteCounty<space>OutCode<space>InCode

If your addresses are typical of UK addresses, many will be missing one
or more of these elements, or have more elements (such as a house name,
or two street names), and it can be very hard for a computer to parse
them.

Can you tell us more about your data?