Separating Address Details

B

Bob Smith

I have received data in the following format , which contains thousands of lines of data grouped together in the one column:(see sample data below)

PO Box 9 PORT NOARLUNGA
PO Box 519 MCLAREN VALE
62 High Street BURNSIDE
2 Sturt Valley Road STIRLING
Main Road PORT PIRIE
1 South Terrace ADELAIDE

I wish to keep all of the street address data (appears in lower case) but delete the suburb data (appears in CAPS).
eg. using the sample data above, the data required is:
PO Box 9
PO Box 519
62 High Street
2 Sturt Valley Road
Main Road
1 South Terrace

Any help would be greatly appreciated.

Kind regards
Bob
 
C

CLR

Assuming there are not "too" many different surburbs, you could use the Edit
Replace feature to pick them off one by one..........just replace "PORT
Click to expand...
NOARLUNGA", with "nothing" (leave the box empty) and > Replace
all...........then on to the next suburb

Vaya con Dios,
Chuck, CABGx3




Bob Smith said:
I have received data in the following format , which contains thousands of
Click to expand...
lines of data grouped together in the one column:(see sample data below)
PO Box 9 PORT NOARLUNGA
PO Box 519 MCLAREN VALE
62 High Street BURNSIDE
2 Sturt Valley Road STIRLING
Main Road PORT PIRIE
1 South Terrace ADELAIDE

I wish to keep all of the street address data (appears in lower case) but
Click to expand...
delete the suburb data (appears in CAPS).
 
D

Dave Peterson

Depending on what those suburbs look like, this macro may work:

Option Explicit
Sub testme()

Dim LastChar As Long
Dim myCell As Range
Dim iCtr As Long

For Each myCell In Selection.Cells
With myCell
LastChar = Len(.Value)
For iCtr = Len(.Value) To 1 Step -1
If Mid(.Value, iCtr, 1) Like "[a-z0-9]" Then
LastChar = iCtr
Exit For
End If
Next iCtr

.Offset(0, 1).Value = Trim(Left(.Value, iCtr))
.Offset(0, 2).Value = Trim(Mid(.Value, iCtr + 1))

End With
Next myCell

End Sub

Make sure you have two cells to the right available. (I kept that suburb part.
You can delete it later if you really want to. I'd keep it until I was
absolutely positive!)

It starts at the far right and goes backward looking for a-z or 0-9. If it
finds one, it knows where to split.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top