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 **********

' ---