S
Smoke
Hello everyone,
i have a large list of cells with data in the format below:
123 BLACKBURN ROAD GLEN WAVERLEY
6 CAROL STREET SCORESBY
345 KIERS AVENUE MOUNT WAVERLEY
4 & 5 / 23 BLACKWOOD DRIVE HAMPTON PARK
34 HUNTINGDALE ROAD MOUNT WAVERLEY
234 CASSINIA AVENUE ASHWOOD
213 Springvale Rd. Springvale
232 Tucker St. Ormond
my aim is to automatically separate the suburb from the stree
address,example:
split 123 BLACKBURN ROAD and GLEN WAVERLEY into two cells
(address) |||||||||||||||||||||||||||||||||||||||(suburb)
6 CAROL STREET ||||||||||||||||||||||||||||||SCORESBY
345 KIERS AVENUE||||||||||||||||||||||||||||MOUNT WAVERLEY
4 & 5 / 23 BLACKWOOD DRIVE||||||||||||||||||||||||HAMPTON PARK
213 Springvale Rd. ||||||||||||||||||||||||||||Springvale
232 Tucker St. |||||||||||||||||||||||||||||||||Ormond
I have tried using the Text-to-Column wizard, but the allowed delimite
is only 1 character. The only possible way i see it is to search eac
cell for a delimiter(from a list of many delimiter options) and spli
it into two cells, with the delimiters being every possible "STREET
"St." "ROAD" "DRIVE" "Rd." etc. That way everything after a "STREET
will be regarded as a suburb and it will be separated.
Notes: suburbs can be with 1, 2 or 3 words, streets can have 2 or mor
words in them
i have a column of the suburbs if that helps.
It might be an idea to convert all to uppercase or each first word wit
upper case.
I am using MSExcel 2000, but can try using the newest.
(sorry i didn't make this pretty i don't know how.
i have a large list of cells with data in the format below:
123 BLACKBURN ROAD GLEN WAVERLEY
6 CAROL STREET SCORESBY
345 KIERS AVENUE MOUNT WAVERLEY
4 & 5 / 23 BLACKWOOD DRIVE HAMPTON PARK
34 HUNTINGDALE ROAD MOUNT WAVERLEY
234 CASSINIA AVENUE ASHWOOD
213 Springvale Rd. Springvale
232 Tucker St. Ormond
my aim is to automatically separate the suburb from the stree
address,example:
split 123 BLACKBURN ROAD and GLEN WAVERLEY into two cells
(address) |||||||||||||||||||||||||||||||||||||||(suburb)
6 CAROL STREET ||||||||||||||||||||||||||||||SCORESBY
345 KIERS AVENUE||||||||||||||||||||||||||||MOUNT WAVERLEY
4 & 5 / 23 BLACKWOOD DRIVE||||||||||||||||||||||||HAMPTON PARK
213 Springvale Rd. ||||||||||||||||||||||||||||Springvale
232 Tucker St. |||||||||||||||||||||||||||||||||Ormond
I have tried using the Text-to-Column wizard, but the allowed delimite
is only 1 character. The only possible way i see it is to search eac
cell for a delimiter(from a list of many delimiter options) and spli
it into two cells, with the delimiters being every possible "STREET
"St." "ROAD" "DRIVE" "Rd." etc. That way everything after a "STREET
will be regarded as a suburb and it will be separated.
Notes: suburbs can be with 1, 2 or 3 words, streets can have 2 or mor
words in them
i have a column of the suburbs if that helps.
It might be an idea to convert all to uppercase or each first word wit
upper case.
I am using MSExcel 2000, but can try using the newest.
(sorry i didn't make this pretty i don't know how.