Breaking up an address column

[John Baker]

Can anyone please help.

I have a column which contains addresses.
eg : 200 Flinders St Melbourne VIC 3000
I would like to place the street, City, state and postcode into 4 different
columns.
Is there an easy way to do this?

I have used (Data/Text to columns) from the menu, and then delimited with a
space
but this separates the street number,street name and street type.

Thanks in anticipation.

John Baker

[Jim Rech]

When it comes to parsing text I try to find a methodology that works for the
majority of cases and then fix the rest manually or with some other
methodology.

So I'd make the assumption that it is the first 2 spaces you want to ignore
in parsing (and then fix exceptions like "123 St. John Street" manually.

Say your addresses start in A1 and go down. In B1 enter this formula:
=SUBSTITUTE(A1," ","!",1). Then copy this formula to C1. You should see
that the resulting string has the first 2 spaces replaced by exclamation
points (!).

Copy the formulas down along side all the addresses in column A and calc
Excel if necessary to update them.

Now you must convert column C from formulas to text. Select all the
formulas in column C. Copy them (Ctrl-c). Then (leaving column C selected)
use Edit, Paste Special and select Values and OK. Press Esc.

The next step is to use Data, Text to Columns, as you did before, with the
space as the parsing character.

Lastly select the street addresses in column D and do an Edit, Replace.
Replace the ! with a space. Then you can delete all the stuff to the left
of column D.

--
Jim Rech
Excel MVP

[David McRitchie]

Hi John,
How do come about getting such data with no
commas or other distinctive separations.

The problem is the separation between the street
and the town. The use of two macros in
http://www.mvps.org/dmcritchie/excel/join.htm
would take care of most of this. You could do
a global replacement first (ctrl+H)
" St " "St,"
" Ave " "Ave,"
to separate the most difficult part, then examine
to make sure you end up with exactly two columns
after running text to columns to separate those.

SepTerm to separate the street address
SepLastTerm to separate the zip at the end
SepLastTerm to separate the state at the end

With each of the above insert an empty column
to the right before using the macro.

If you plan on doing this often you could write a
macro to incorporate the above for your specific
columns.

Sub SplitAddressing()
'-- include St Ave Ct Cir Blvd etc. as needed
Columns("A:A").Replace What:=" St ", _
Replacement:=" St,", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("A:A").Replace What:=" Ave ", _
Replacement:=" Ave,", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("A:A").Replace What:=" Ct ", _
Replacement:=" Ct,", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

'-- if you had US states that you don't want to separate
Columns("A:A").Replace What:=" New York ", _
Replacement:=" New_York", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("A:A").TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))

Columns("B:B").Insert Shift:=xlToRight
Columns("A:A").Select
SepTerm

Columns("C:C").Select
SepLastTerm

Columns("d:d").Insert Shift:=xlToRight
Columns("C:C").Select
SepLastTerm

Columns("A:E").EntireColumn.AutoFit
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Baker" <jba...@ihug.com.au> wrote in message news:u0bvOcq5BHA.2220@tkmsftngp04...

[Myrna Larson]

Here's a VBA function for Excel 2000 or 2002* that will handle most cases. It assumes there are
a least 4 "words" in the full address, separated by spaces. The *last* word 3 words are the
city, state/province, and postal code, respectively. The first n - 3 words constitute the street
address.

To use this, add a VBA module to your workbook and paste the code below into it.

Let's say the full address is in B2. Select 4 adjacent cells in a row, say C2:F2, and type this
formula =SplitAddress(B2). Hold down the CTRL and SHIFT keys while you press ENTER.

If the name of either the city or province is more than 2 words (e.g. New South Wales), this
won't work correctly. You'll have to fix those manually. In such a case, I would suggest
replacing the data before you split it: replace New South Wales with New_South_Wales or NSW.
Then do the split, then change back to New South Wales.

After you've split the data, you may want to convert it to values (Edit/Copy the split data,
then Edit/Paste Special and select the Values option).

* It won't work with XL97 or earlier because it uses the Split and Join functions that were
added in XL2000.

Function SplitAddress(FullAddress As String) As Variant
Dim City As String
Dim n As Long
Dim State As String
Dim StreetAddr As String
Dim v As Variant
Dim ZipCode As String

v = Split(Application.Trim(FullAddress), " ")
n = UBound(v)

If n < 3 Then
SplitAddress = CVErr(xlErrValue)
Else
ZipCode = v(n)
State = v(n - 1)
City = v(n - 2)
ReDim Preserve v(n - 3)
StreetAddr = Join(v, " ")
SplitAddress = Array(StreetAddr, City, State, ZipCode)
End If

End Function

[John Baker]

Thanks very much for you reply Myrna.
What you have given me works fantastically for addresses with spaces
separating street/city etc.
However I seem to have a major problem.
An address such as "233 Rathdowne St Carlton VIC 3053" doesn't seem to
contain spaces between "St" "Carlton" "VIC" and "3053". I am not sure which
character has been used. Is there any way of telling? I have downloaded the
addresses from the net and have pasted them from internet explorer into
excel.

Thanks for any help.
John Baker

"Myrna Larson" <myrna...@chartermi.net> wrote in message
news:gu0ubuo0ilsgjahv9...@4ax.com...

[David McRitchie]

You have &nbsp; char(160) within
=CODE(Mid($a$1,Row(),1) =MID($A$1,Row(),1)
50 2
51 3
51 3
160
32
82 R
97 a
116 t
104 h
100 d
111 o
119 w
110 n
101 e
32
83 S
116 t
160
32
67 C
97 a
114 r
108 l
116 t
111 o
110 n
32
86 V
73 I
67 C
32

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Baker" <jba...@ihug.com.au> wrote in message news:eRtshVz5BHA.1016@tkmsftngp07...