Microsoft KB Archive/168798: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - ">" to ">")
Line 141: Line 141:
       '
       '
         P = InStr(S, ",")
         P = InStr(S, ",")
         If P > 0 Then
         If P > 0 Then
           City = Trim$(Left$(S, P - 1))
           City = Trim$(Left$(S, P - 1))
           S = Trim$(Mid$(S, P + 1))
           S = Trim$(Mid$(S, P + 1))
Line 148: Line 148:
       '
       '
           P = InStr(S, ",")
           P = InStr(S, ",")
           If P > 0 Then
           If P > 0 Then
             State = Trim$(Left$(S, P - 1))
             State = Trim$(Left$(S, P - 1))
             Zip = Trim$(Mid$(S, P + 1))
             Zip = Trim$(Mid$(S, P + 1))

Revision as of 19:40, 20 July 2020

Knowledge Base


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

  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
                            
  2. 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
                            
  3. 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