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

 

[plettieri]

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

Hi:

Just an idea. I am not sure if this would work.....as you suggest...set up a UDF for all possibles St., Street, Ave, Blvd, Avenues, etc....then call for the UDF function in the address and parse the address at that insertion.

Just an idea...

plettieri

 

[parry]

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

Hi Smoke, welcome to the board!

Yes, you are right that the data will have to have some uniformity so you should make all the data Uppercase (use the Upper function to do this) plus abbreviations such as st. and rd. should be changed as well. To quickly fix the st. part use Excels Find/Replace function to update St. to STREET for example.

The following is an example of the formula you coould use..

Book1
	A	B	C	D
1	Data	Address1	Suburb
2	123BLACKBURNROADGLENWAVERLEY	123BLACKBURNROAD	GLENWAVERLEY
3	6CAROLSTREETSCORESBY	6CAROLSTREET	SCORESBY
4	345KIERSAVENUEMOUNTWAVERLEY	345KIERSAVENUE	MOUNTWAVERLEY
Sheet1

hth

 

[PaddyD]

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

"i have a column of the suburbs if that helps. "

...i would suggest that this could probably be exploited!

 

[plettieri]

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

PaddyD,

Just as a matter of interest, how would would you exploit this, theorically, that is.

plettieri

 

[sbendbuckeye]

Here's another thought, although pretty low tech by the above standards.

1. Create a string (UCased) of possibilities
strAddrInd = "STREET,ST,BOULEVARD,BLVD,ROAD,RD," etc.
You might even want to stick them into a hidden sheet
and pull them in as a range so you can easily update the
list as new ones become known.

2. Save original address data
strOrigAddr = strAddr
varOrigArray = Split(strAddr)

3. Strip all punctuation out of the current address
strAddr = Replace$(strAddr, ".", vbNullString), etc

4. Use split (assuming Access2000 or later) to create an Addr
array and then process it in reverse until you match. If the
matching array element is not the upper bound, then all
array elements from current + 1 to upper bound would be
the suburb.
strAddr = vbNullString
strTheBurbs = vbNullString
varArray = Split(strAddr)
For lngX = UBound(varArray) to LBound(varArray) Step -1
If InStr(strAddrInd, UCase$(varArray(lngX))) > 0 Then
If lngX = UBound(varArray) Then
strAddr = strOrigAddr
Else
'Create address string from original address array
For lngX = LBound(varArray) To lngX
strAddr = strAddr & varOrigArray(lngx)
Next lngX
'Create suburbs string from original address array
For lngX = lngX + 1 To UBound(varArray)
strTheBurbs = strTheBurbs & varOrigArray(lngX)
Next lngX
End If
Exit For
End If
Next lngX

 

[Smoke]

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

Thanks to everyone for the replies and interest.

Plettieri's idea is my aim, but i have no idea how ot implement it.

Im Parry's response the formula is a good idea, although it only works for the "STREET, AVENUE and ROAD" possibilities. How can i manipulate it to pick from a list of street ends.

about the list of suburbs(around 3000) it may be possible to look it up and remove it from the text string and paste it into a cell.

sbendbuckeye's solution sounds right but I am too unexperienced to implement anything past point 1.

 

[dk]

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

Hello mate,

I've written two UDFs which may be of use to you - GetStreetAddress and GetSuburb. Here they are in action:-

Book17
	A	B	C	D
1	FullAddress	StreetAddress	Suburb
2	123BLACKBURNROADGLENWAVERLEY	123BLACKBURNROAD	GLENWAVERLEY
3	6CAROLSTREETSCORESBY	6CAROLSTREET	SCORESBY
4	345KIERSAVENUEMOUNTWAVERLEY	345KIERSAVENUE	MOUNTWAVERLEY
5	4&5/23BLACKWOODDRIVEHAMPTONPARK	4&5/23BLACKWOODDRIVE	HAMPTONPARK
6	34HUNTINGDALEROADMOUNTWAVERLEY	34HUNTINGDALEROAD	MOUNTWAVERLEY
7	234CASSINIAAVENUEASHWOOD	234CASSINIAAVENUE	ASHWOOD
8	213SpringvaleRd.Springvale	213SpringvaleRd.	Springvale
9	232TuckerSt.Ormond	232TuckerSt.	Ormond
Sheet1

Here is the code required for these. Paste this into a standard module:-

Option Explicit

Private StreetTypes As Variant

Private Sub GetStreetTypes()
    StreetTypes = Array("Avenue ", "Court ", "Place ", "Close ", "Street ", "Parade ", "Road ", "Drive ", "St. ", "Rd. ")
End Sub


Public Function GetStreetAddress(Address)
    Dim lngElement As Long
    Dim lngParsePos As Long

    GetStreetTypes

    For lngElement = LBound(StreetTypes) To UBound(StreetTypes)
        lngParsePos = 0

        lngParsePos = InStr(1, Address, StreetTypes(lngElement), vbTextCompare)

        If lngParsePos > 0 Then    'Match Found!
            GetStreetAddress = Left$(Address, lngParsePos - 2 + Len(StreetTypes(lngElement)))
            Exit Function
        End If

    Next lngElement

    GetStreetAddress = "Street Not Found"

End Function


Public Function GetSuburb(Address)
    Dim strStreetAddress As String

    strStreetAddress = GetStreetAddress(Address)

    If strStreetAddress = "Street Not Found" Then GetSuburb = "Suburb Not Found": Exit Function

    GetSuburb = Right(Address, Len(Address) - Len(strStreetAddress) - 1)

End Function

You'll need to modify the GetStreetTypes procedure to include any street types that I've missed. I included a space after each one to try and reduce the chance of an error.

 

[Smoke]

Hello Dan and thanks for your reply (fellow Australian).

would your solution work with excel 2000? as i noticed your operating system is WINXP so i assume you have the newer version of MS Office.

I pasted your code and formula and recieved a #NAME?, ill try to figure out where it comes from.

Thanks again.

 

[parry]

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

Hi again, heres a UDF that should do it. This is a function called Halve and it accepts three arguments...
#1 = Cell you are looking at for the text
#2 = Range that has the values to look for within the 1st argument
#3 = True = Return the first half (including the text your looking for)
False = Return the 2nd half of the string

eg =HALVE(A1,B2:B10,TRUE) means look in cell A1 for a value and compare the values in B2:B10 against this and if found return the first half of the string. If you have multiple hits (eg you have Street and road in the same text) then the result will be the first value it finds in the given range (ie B2:B10 in the example).

If no value is found the result will be a blank cell.

To Add the code to a Module do the following:-
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)

Function Halve(Txt As String, Rng As Range, Optional First As Boolean) As String
Dim c, Str() As String, i As Integer

For Each c In Rng
i = InStr(1, UCase(Txt), UCase(c))
    If i > 0 Then
    Txt = Left(Txt, i + Len(c) - 1) & "~" & Mid(Txt, i + Len(c), Len(Txt) - i + Len(c))
    Str = Split(Txt, "~", -1, vbTextCompare)
        If First = True Then
            Halve = Str(LBound(Str))
            Exit Function
        Else
            Halve = Str(UBound(Str))
            Exit Function
        End If
    End If
Next c

End Function

Heres an example as well

Halve.xls
	A	B	C	D
1	DATA	1stHalf	2ndHalf
2	123BLACKBURNROADGLENWAVERLEY	123BLACKBURNROAD	GLENWAVERLEY
3	6CAROLSTREETSCORESBY	6CAROLSTREET	SCORESBY
4	345KIERSAVENUEMOUNTWAVERLEY	345KIERSAVENUE	MOUNTWAVERLEY
5	4&5/23BLACKWOODDRIVEHAMPTONPARK	4&5/23BLACKWOODDRIVE	HAMPTONPARK
6	34HUNTINGDALEROADMOUNTWAVERLEY	34HUNTINGDALEROAD	MOUNTWAVERLEY
7	234CASSINIAAVENUEASHWOOD	234CASSINIAAVENUE	ASHWOOD
8	213SpringvaleRd.Springvale	213SpringvaleRd.	Springvale
9	232TuckerSt.Ormond	232TuckerSt.	Ormond
10
11		Criteria
12		ROAD
13		STREET
14		AVENUE
15		DRIVE
16		Rd.
17		St.
Sheet1