Microsoft KB Archive/168798

= How To Parse City, State, and Zip Code into Separate Values =

Article ID: 168798

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Control Creation Edition
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
 * Microsoft Visual Basic 2.0 Standard Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 2.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Word 97 Standard Edition
 * Microsoft PowerPoint 97 Standard Edition

-



This article was previously published under Q168798



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.

Examples of supported inputs:

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

If at least one comma is present, it is presumed to be between City and State, and the fields are parsed in a different order:

City, State, Zip Code

Step-by-Step Example
  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   To test, create a form with four text boxes (txtAddress, txtCity, 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  Display the form, type an address into txtAddress, and click the command button. The other three fields should contain the parsed values.

Keywords: kbhowto kbprogramming KB168798

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.