seperate components of a whole address entry

T

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

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
 
T

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
 
J

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
Click to expand...
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
 
J

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
Click to expand...
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
 
T

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!
 
T

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!
 
R

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
Click to expand...

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
 
R

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address 1
Moving part of a cell to another cell 2
Seperate Name, Street Address & City, State Zip 5
Obtaining information from a database for a worksheet 4
Parsing an address field 2
finding the number of occurences 2
Deleting rows with same address 2
formulate email address based on last name & first letter of first 1

Top