Hi
Can anyone help me with a formula for Excel 2007 to separate the colum
in to multiple colum ?
below the examples and i need to column to separate the column
Address, City, Stae, Zip code
Eg:
711 B East 10th Street Anniston AL 36207
785 N. Dean Road Suite 600 Auburn AL 36830
401 Tuscaloosa Avenue, Southwest Suite 210 Birmingham AL 35211
1830 14th Ave. South Birmingham AL 35205
1930 Edwards Lake Road Birmingham AL 35235
4515 Southlake Parkway Suite 150 Birmingham AL 35244
2224 Cahaba Valley Drive Suite A-1 Birmingham AL 35242
The problem is that there are no reliable separators between the end of the
address segment and the beginning of the City.
Here is one way around that:
1. Parse out the zip code which is always at the end.
In your examples, they are all 5 digits and I will assume that.
2. Do a reverse lookup on the zip code and return all the cities associated
with it, and there can be a number of them (35235 has seven).
Although you can do the lookup at the USPS site, this will take time.
If you can create or purchase a table of values, you could execute this much
faster. With long lists, it can be a problem.
3. Check your address string looking for the city. This part depends on the
city being entered the same as on the USPS site -- i.e. no misspellings.
4. Now you can parse the string:
Address = LEFT(string, start_of_city_name)
City = from the reverse zip lookup
State = from the reverse zip lookup, or the second word from the end.
Zip = last word.
Here is a VBA macro that will do this, doing the lookup at the USPS web site.
If this is going to be widely used, and you want to use this method, some
additional error checking may be of value. You may need to check for properly
formed zip codes; and you'll also need some routines in case there is not a
valid lookup, IE freezes, misspelled city name in your data, etc.
I only checked the city and not the state. I assumed that all zip codes were
"unistate". If this is not the case, then you should also check the state.
But if you are going to do this a lot, strongly consider obtaining or creating
a proper database, and then using lookups to find the city-zip matches.
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.
*** At the top of the macro, there are instructions for setting two references.
*** This is most important, and the macro will not run if these are not set.
To use this Macro (Sub), first select the range of cells which have data to be
parsed. Then <alt-F8> opens the macro dialog box. Select the macro by name,
and <RUN>.
==================================================
Option Explicit
Option Base 0
'set reference to Microsoft VBScript Regular Expressions 5.5
'Set reference to Microsoft Internet Controls
' In Excel 2007, this may be called "Microsoft Browser Helpers"
Dim re As RegExp, mc As MatchCollection
Sub ParseAddr()
Dim c As Range, rg As Range
Dim sZip5 As String
Dim aTemp As Variant
Dim aCityState As Variant
Dim sCity As String, sState As String
Dim sAdr As String
Dim i As Long
Set rg = Selection
Application.ScreenUpdating = False
For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 4)).ClearContents
aTemp = Split(Trim(c.Value), " ")
sZip5 = aTemp(UBound(aTemp))
aCityState = RevZip(sZip5)
i = 0
Do Until InStr(1, c.Value, aCityState(0, i), vbTextCompare) > 0
i = i + 1
Loop
sCity = aCityState(0, i)
sState = aCityState(1, i)
sAdr = Left(c.Value, InStr(1, c.Value, aCityState(0, i), vbTextCompare) -
1)
c.Offset(0, 1).Value = sAdr
c.Offset(0, 2).Value = sCity
c.Offset(0, 3).Value = sState
'set zip format to text to retain leading 0's
c.Offset(0, 4).NumberFormat = "@"
c.Offset(0, 4).Value = sZip5
c.Offset(0, 4).Errors(XlErrorChecks.xlNumberAsText).Ignore = True
Next c
Range(rg(1, 2), rg(1, 5)).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
'-----------------------------------------------------
Function RevZip(sZip5 As String) As Variant
'returns 2D array of each city/state pair
'in the zip code
'Set reference to Microsoft Internet Controls
' In Excel 2007, this is called "Microsoft Browser Helpers"
Dim IE As InternetExplorer
Const sURL As String = "
http://zip4.usps.com/zip4/citytown_zip.jsp"
Dim sHTML As String
Dim sTemp() As String
Dim i As Long
' Group2 = City Group3=State IGNORE CASE
Const rePattern As String = "headers=pre>(<b>)?([^,]+),\s([^<]+)"
Dim lNumCities As Long
Application.Cursor = xlWait
Set IE = New InternetExplorer
IE.Navigate sURL
IE.Visible = False
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
IE.Document.all("zip5").Value = sZip5
IE.Document.all("Submit").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
sHTML = IE.Document.body.innerhtml
IE.Quit
Application.Cursor = xlDefault
'Note that the USPS site can return multiple
'cities for each zip code. So we need to
'return them all
lNumCities = RegexCount(sHTML, rePattern)
ReDim sTemp(0 To 1, 0 To lNumCities - 1)
For i = 0 To lNumCities - 1
sTemp(0, i) = RegexMid(sHTML, rePattern, i + 1, 2)
sTemp(1, i) = RegexMid(sHTML, rePattern, i + 1, 3)
Next i
RevZip = sTemp
End Function
'-------------------------------------------------------------
Private Function RegexMid(s As String, sPat As String, _
Optional Index As Long = 1, _
Optional Subindex As Long, _
Optional CaseIgnore As Boolean = True, _
Optional Glbl As Boolean = True, _
Optional Multiline As Boolean = False) As String
Dim i As Long
Set re = New RegExp
With re
.Pattern = sPat
.IgnoreCase = CaseIgnore
.Global = Glbl
.Multiline = Multiline
If .Test(s) = True Then
Set mc = .Execute(s)
If Subindex = 0 Then
RegexMid = mc(Index - 1)
ElseIf Subindex <= mc(Index - 1).SubMatches.Count Then
RegexMid = mc(Index - 1).SubMatches(Subindex - 1)
End If
End If
End With
Set re = Nothing
End Function
'--------------------------------------------------------------
Private Function RegexCount(s As String, sPat As String) As Long
Set re = New RegExp
With re
.Pattern = sPat
.Global = True
.IgnoreCase = True
Set mc = .Execute(s)
End With
RegexCount = mc.Count
Set re = Nothing
End Function
===========================================
--ron
