Advanced Parse - separate address from suburb in two cells

[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 street 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 delimiter is only 1 character. The only possible way i see it is to search each cell for a delimiter(from a list of many delimiter options) and split 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 more 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 with 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.)

 

[parry]

Re: Advanced Parse - separate address from suburb in two cel

oops 2 posts b4 I had the chance to post. Oh well, you have another option then Smoke.

 

[Smoke]

Re: Advanced Parse - separate address from suburb in two cel

Thank you Parry i found your solution working perfectly and easy enough for me to implement
thanks for your time.

 

[parry]

Re: Advanced Parse - separate address from suburb in two cel

Your most welcome. I like Dans option too.

btw, the 2nd argument is not case sensitive so you can enter your criteria as ROAD or road and they should both work OK.

Spot ya.

 

[brettdj]

Hi Smoke,

A fellow Victorian?

The code below is a little more complex than what your example called for but it tests the addresses for validity as it extracts.

If you select your list then run the macro it parses the address into the two columns to the immediate right

It looks for
(first character number)(space...Text String)(space....Street Type)(Remaining text)
If the string doesn't meet the criteria it puts an "invalid address" to the immediate right of the string

Sub GetChars()
Dim RegX As Object, RegO As Object, RegSub As Object, Cel As Range
    Set RegX = CreateObject("vbscript.regexp")
    RegX.ignorecase = True
    RegX.Global = True
    RegX.Pattern = "(\d{1}|\d{1}.+)(\s{1}\D+\s{1}.*)(Avenue|Court|Place|Close|Street|Parade|Road|Drive|St.|St|Rd.|Rd)(.+)"
    For Each Cel In Selection
        Set RegO = RegX.Execute(Cel.Value)
        If RegO.Count = 1 Then
            Cel.Offset(0, 1) = RegO(0).submatches(0) & RegO(0).submatches(1) & RegO(0).submatches(2)
            Cel.Offset(0, 2) = RegO(0).submatches(3)
        Else
            Cel.Offset(0, 1) = "Invalid address"
        End If
    Next
End Sub

Cheers

Dave