On Thu, 28 May 2009 21:30:01 -0700, telly wrote:

Hi Jocob
I really appreciate your prompt response.
Look at this address list which is in excel.
I need to seperate the street number and name from the suburb and the suburb
from the state and the state from the postcode.
You have no idea what sort of problem that you can resolve if you answer.

example: 1 - first addres

street number&name (11 GAVIN PL)
(Suburb KINGS LANGLEY )
State (NSW)
Post code (2147)

example: 2 - fourth address

street number&name (PO BOX 128)
Suburb ( MEENIYAN)
State (VIC)
Post code (3956)

here are a short list of address

11 GAVIN PL KINGS LANGLEY NSW 2147
27/1 BRIDGEMAN DR REEDY CREEK QLD 4227
65/3 BRIDGMAN DR REEDY CREEK QLD 4227
PO BOX 360 MENAI CENTRAL BANGOR NSW 2234
PO BOX 128 MEENIYAN VIC 3956
25/1 BRIDGMAN DR REEDY CREEK QLD 4227


kind regards
--
Telly

This can be quite a difficult problem, unless you have some standardized method
of determining where the street numbername ends, and the suburb begins.

Alternatively, and workable in the US (but I don't know about Australia), you
could determine the suburb name by doing a lookup on the postcode.

The algorithm for the postcode is simple since the postcode is always the last
set of digits in the line. And the State is the string of capitalized letters
just before that.

In your examples, it is the case that the StreetNumberName is either a PO Box
ending with a Number; or a true street address ending with DR or PL.

If that is always the case (and you could certainly add on some additional
street name terminations (see below), then a macro could parse out the
segments.

There are instructions within the macro as to how to add more terminators. The
macro, as written, assumes all capital letters, single spaces between words,
and no spaces at the beginning or end of the string. This could be changed if
necessary.

If there are too many exceptions to the PO BOX or Street Terminator rule,
you'll have to do a lookup on the postcode to get the Suburb, and parse things
out that way.


To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range to parse. Then <alt-F8 opens
the macro dialog box. Select the macro by name, and <RUN.

=============================================
Option Explicit

Sub ParseAUaddress()
Dim c As Range, rg As Range
Dim S As String
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")

'Note the enclosed pipe delimited list of StreetNumberName
'terminators below: (PL|DR)
'To expand this, be sure to add further pipe delimited phrases
'within the parentheses like this: (PL|DR|AV|ST)

re.Pattern = "^(.*?\s(PL|DR)|PO\sBOX\s\d+)\s(.*?)\s([A-Z]+)\s(\d+)$"

Set rg = Selection 'or whatever
For Each c In rg
With c
Range(.Offset(0, 1), .Offset(0, 4)).ClearContents
S = .Value
If re.test(S) Then
Set mc = re.Execute(S)
For Each m In mc
.Offset(0, 1).Value = m.submatches(0) 'StreetNumberName
.Offset(0, 2).Value = m.submatches(2) 'Suburb
.Offset(0, 3).Value = m.submatches(3) 'State
.Offset(0, 4).Value = m.submatches(4) 'PostCode
Next m
End If
End With
Next c
End Sub
==============================
--ron