Advanced Parse - separate address and suburb in cell

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.
 
F

Frank Kabel

Hi
I think this can't be done fully automatically. I'm pretty sure that
you have streetnames not ending with 'street', 'avenue', 'drive' but
with a random word.
But lets assume you have this complete list of 'street ends'. Try the
following:
1. Enter all your streen endings in a range (e.g. D1:D10) and define a
name for this range (goto 'Insert - name - Define name'). Lets assume
the name 'streetends'
2. If your text is stored in column A enter the following in B1 (to get
the suburb). It has to be entered as array formula (CTRL+SHIFT+ENTER)
=TRIM(MID(A1,FIND("
",A1,MAX(IF(ISERROR(FIND(streetends,A1)),0,FIND(streetends,A1))))+1,102
4))
copy down
3. To get the street name enter the following in C1 (also as array
formula):
=TRIM(LEFT(A1,FIND("
",A1,MAX(IF(ISERROR(FIND(streetends,A1)),0,FIND(streetends,A1))))-1))
copy down

Frank
 
S

Smoke

Hi frank, and thanks for your reply.

I tried your formulas but get #VALUE!, i pasted them wit
crtl-shift-enter and excel put brackets around.
i could send you an example xls file, i had no luck using you
solution.

i have also posted the question on another board, you may want to hav
a peak.
http://www.mrexcel.com/board2/viewtopic.php?p=368014#javascript:void(0)



The list of street ends is around 10-13 and your solution is very goo
because i can add new ones when they appear.

Thanks
 
F

Frank Kabel

Hi
some ideas:
- have you put the formula in one line (that is removed the linebreaks
due to your newsreader)
- laso not that in the part
=TRIM(MID(A1,FIND(" ",A1....
FIND is searching for a space

If this don't work mail me your sheet
Frank
 
Top