Here is some sample code to parse out the City State and ZIP form a string.

Example usage:

? GetCity("Some Town, OK 73101")
Some Town
? GetCity("Some Town OK 73101")
Some Town

? GetState("Some Town, OK 73101")
OK
? GetState("Some Town OK 73101")
OK

? GetZIP("Some Town, OK 73101-0000")
73101-0000
? GetZIP("Some Town, OK 73101")
73101

Following is the VBA code for the functions used above:

Code:

' ******** Code Start ********
'This code was originally written by Boyd Trimmell aka HiTechCoach.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of 'Boyd Trimmell
'

Public Function FlipIt(InData As String) As String

Dim p As Integer
Dim strOut As String

strOut = ""
For p = Len(InData) To 1 Step -1

strOut = strOut & Mid(InData, p, 1)

Next p

FlipIt = strOut


End Function


Public Function GetCity(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = FlipIt(Trim(InData))
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end


strWRK = Trim(Mid(strWRK, p))
p2 = InStr(strWRK, " ")

If p2 > 0 Then
' found state end

strWRK = Trim(Mid(strWRK, p2))

' found city
strOut = strWRK

' remove a , from end of city
If Left(strOut, 1) = "," Then
strOut = Trim(Mid(strOut, 2))
End If

strOut = FlipIt(strOut)


End If


End If

GetCity = strOut

End Function

Public Function GetState(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = FlipIt(Trim(InData))
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end

strWRK = Trim(Mid(strWRK, p))

p2 = InStr(strWRK, " ")
' found state end

strOut = FlipIt(Trim(Left(strWRK, p2)))


End If

GetState = strOut

End Function

Public Function GetZIP(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = FlipIt(Trim(InData))
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end
p2 = InStr(p, strWRK, " ")

strOut = FlipIt(Trim(Left(strWRK, p)))


End If

GetZIP = strOut

End Function
' ********** Code End **********