how do i split an address field into two fields ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have imported an excel file into access and i have around 3000 address. i
need to split the address field into two fileds by using the space in between
the streeet number and the street name.
for example:
all my records look like this: (122 Smith st)--- one field
i would like to split it into (122) --1st field and (Smith st) -- 2nd field
 
If all of your addresses really do look like that (number followed by space
followed by street) then you simply need to take everything to the left of
the first space as the number, and everything to the right of the first
space as the street ...

UPDATE Table1 SET Table1.[Number] = Left$([Address],InStr(1,[Address],"
")-1), Table1.Street = Mid$([Address],InStr(1,[Address]," ")+1);

Of course, if any of your addresses deviate from that pattern - and if you
have 3000 of them it will be very surprising if none of them do - then
things can become a lot more complicated.

But at least the above example should do *most* of the work for you.
 
Back
Top