HOWTO: Parse City, State, and Zip Code into Separate Values

ID: Q168798

The information in this article applies to:

• Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
• Microsoft Visual Basic Standard and Professional Editions for Windows, versions 2.0, 3.0
• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
• Microsoft Excel 97 for Windows
• Microsoft Word 97 for Windows
• Microsoft PowerPoint 97 for Windows

SUMMARY

This article provides a procedure for parsing a single variable containing US City, State, and Zip Code information into three separate variables. The routine supports:

• Either 5- or 9-digit zip code.
• Multi-word state names (if preceded by a comma).
• Any number of intermediate spaces.

   New York, New York, 99999
   New York, NY, 99999-9999
   New York, NY 99999-9999
   New York NY 99999

MORE INFORMATION

WARNING: ANY USE BY YOU OF THE CODE/MACRO PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code/macro "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. For versions of BASIC that don't support line-continuation characters, remove the underscore from the end of the line and merge with the following line when re-creating this code.

Fields are parsed in the following order if no commas are found in the address:

   Zip Code, State, City

   City, State, Zip Code

Step-by-Step Example

1. Enter the following code:

      Function CutLastWord (ByVal S As String, Remainder As String) _
         As String
      ' CutLastWord: returns the last word in S.
      ' Remainder: returns the rest.
      '
      ' Words are separated by spaces
      '
      Dim  I As Integer, P As Integer
        S = Trim$(S)
        P = 1
        For I = Len(S) To 1 Step -1
          If Mid$(S, I, 1) = " " Then
            P = I + 1
            Exit For
          End If
        Next I
        If P = 1 Then
          CutLastWord = S
          Remainder = ""
        Else
          CutLastWord = Mid$(S, P)
          Remainder = Trim$(Left$(S, P - 1))
        End If
      End Function

      Sub ParseCSZ (ByVal S As String, City As String, State As String, _
                    Zip As String)
      Dim P As Integer
      '
      ' Check for comma after city name
      '
        P = InStr(S, ",")
        If P > 0 Then
          City = Trim$(Left$(S, P - 1))
          S = Trim$(Mid$(S, P + 1))
      '
      '   Check for comma after state
      '
          P = InStr(S, ",")
          If P > 0 Then
            State = Trim$(Left$(S, P - 1))
            Zip = Trim$(Mid$(S, P + 1))
          Else                      ' No comma between state and zip
            Zip = CutLastWord(S, S)
            State = S
          End If
        Else                        ' No commas between city, state, or zip
          Zip = CutLastWord(S, S)
          State = CutLastWord(S, S)
          City = S
        End If
      '
      ' Clean up any dangling commas
      '
        If Right$(State, 1) = "," Then
          State = RTrim$(Left$(State, Len(State) - 1))
        End If
        If Right$(City, 1) = "," Then
          City = RTrim$(Left$(City, Len(City) - 1))
        End If
      End Sub

   txtState, txtZip), and a command button. Add the following code:

      Sub Command1_Click()
      Dim City As String, State As String, Zip As String
        ParseCSZ txtAddress, City, State, Zip
        txtCity = City
        txtState = State
        txtZip = Zip
      End Sub

   button. The other three fields should contain the parsed values.

Keywords          : kbprg kbVBp400 kbVBp500 kbhowto PrgOther VB4WIN vbwin 
Version           : WINDOWS:1.0 1.1 2.0 3.0 4.0 5.0 7.0 97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: October 2, 1997