Nokiren
03-14-2007, 08:49 PM
I am very new to VBA and need a little help writing some code to parse out date from one cell to multiple cells.
Basically I have an address field that that contains Unit Number, Street Number, Street Name and Street Type all in the same cell which is Street name and I need to break these out into their separate columns.
Where all the date is in the column 20 (?T?), I need to break these out into their correct fields
1. Obtain unit number if exists from the format # / #, #/#, #&# or # # into the UNO column 16 (?P?) and removing the / character and any additional spaces.
2. Get the street number from the format if #, #Alpha, # - #, #-#, # & # or #&# into the SNO column 17 (?Q?) and remove any additional spaces before the street name.
3. Get any street type that exist in the column Street Name 20 (?T?) into column 19 (?S?)
At this stage I don?t even know what type of code to write up or were to start, so I am after your help if you can.
Here is a short sample of the data
"Bimbinbie" Nerriga Halls La Road
"Koknese"
"THE RIVERSIDE" BOLDING CT
1 18 giufre cres
1 & 2 20 Tenth
1 & 2 45 Albany Ave
1 & 2/35 Coriedale Drive
1 / 1 Tipperary Crt
1 718 East steet
1 ABBOTT ST
102 420 Queen Street
5 228-230 Buckley Street
5 / 242 - 246 Haldon St
C708/6 Brodie Spark Drive
CA7 Section9 Cnr Tennyson Stree
Lot 111 6 Shafston Ave & Castle
Note: I have several large spreadsheets (over 20,000 records) that need to be cleaned before importing into an Access database and really need a VBA solutions.
Thank in Advance :help
Tony
Basically I have an address field that that contains Unit Number, Street Number, Street Name and Street Type all in the same cell which is Street name and I need to break these out into their separate columns.
Where all the date is in the column 20 (?T?), I need to break these out into their correct fields
1. Obtain unit number if exists from the format # / #, #/#, #&# or # # into the UNO column 16 (?P?) and removing the / character and any additional spaces.
2. Get the street number from the format if #, #Alpha, # - #, #-#, # & # or #&# into the SNO column 17 (?Q?) and remove any additional spaces before the street name.
3. Get any street type that exist in the column Street Name 20 (?T?) into column 19 (?S?)
At this stage I don?t even know what type of code to write up or were to start, so I am after your help if you can.
Here is a short sample of the data
"Bimbinbie" Nerriga Halls La Road
"Koknese"
"THE RIVERSIDE" BOLDING CT
1 18 giufre cres
1 & 2 20 Tenth
1 & 2 45 Albany Ave
1 & 2/35 Coriedale Drive
1 / 1 Tipperary Crt
1 718 East steet
1 ABBOTT ST
102 420 Queen Street
5 228-230 Buckley Street
5 / 242 - 246 Haldon St
C708/6 Brodie Spark Drive
CA7 Section9 Cnr Tennyson Stree
Lot 111 6 Shafston Ave & Castle
Note: I have several large spreadsheets (over 20,000 records) that need to be cleaned before importing into an Access database and really need a VBA solutions.
Thank in Advance :help
Tony