seperate components of a whole address entry

[telly]

Hi there


I am looking for ways to:
seperate a whole address entry into different components like:
number & street name
suburb
state
postcode

It would be highly appreciated if you can help . I looked everywhere in help
sections but nothing available for this.The only available is seperate first
name and last name. I have apply the same formula but does not work.


Kind regards
(e-mail address removed)

 

[Jacob Skaria]

Please post few samples

 

[telly]

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

 

[Jacob Skaria]

Without using a macro you will have to do this in sections..Assuming you have
the addresses in ColA...try the below..

1. In B1 enter the below formula which will return the number of spaces in
between.
=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),))

2. Sort the entire list by ColB so that addresses with the same number of
spaces come together.You might end up in 3 or 4 categories with 5,6,7 spaces

3. Now copy the first section of address (with 5 spaces) and paste that to a
differnt sheet. From menu Data>Text to columns>Next>Space Delimiter

Next>Finnish. will split that to different columns..Using CONCATENATE

function you will need to combine some fields...

4. Repeat the same for the ones with 6 and 7 spaces..


If this post helps click Yes

 

[telly]

Hi Jacob

first i appreciate your prompt response and second your answer which makes
sence to me . i will try and see how I go.

Thank you again God bless you mate!

 

[Ron Rosenfeld]

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

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