Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Odenton, MD
    Posts
    2

    Unanswered: Parsing mailing address block with consistent layout.

    I want take a block of text that is always in this format:

    Firstname Lastname
    2018 Street Address Rd
    City, ST 00000-0000

    And split into these fields:

    ship_first, ship_last, ship_address, ship_city, ship_state, ship_zip

    I'm sure this question has been asked a thousand times but the only examples I've been able to find deal with names, or addresses whose format varies.

    This format will always be the same, and I would like to be able to copy it as a text block and plunk it down somewhere, only to be magically split up!

    Thanks in advance for the gobs of wisdom!


    - Paul

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    If you need help on how to populate the three variables in my sample that contain the address lines, let me know.
    Code:
    Sub SplitShippingAddress()
       'sample splitting of address in this format
       'Firstname Lastname
       '2018 Street Address Rd
       'City, ST 00000-0000
       '
       'into ship_first, ship_last, ship_address, ship_city, ship_state, ship_zip
       '
    
       Dim ship_first As String
       Dim ship_last As String
       Dim ship_address As String
       Dim ship_city As String
       Dim ship_state As String
       Dim ship_zip As String
    
       ' placeholders for position in string
       Dim iLocationStart As Integer
       Dim iLocationEnd As Integer
    
       ' populate sample variables with the address
       ' (this could easily be done from a recordset)
       Dim strLine1 As String
       Dim strLine2 As String
       Dim strLine3 As String
    
       '--- if you're going to loop through a recordset
       '--- the loop should start here
       strLine1 = "Firstname Lastname"
       strLine2 = "2018 Street Address Rd"
       strLine3 = "City, ST 00000-0000"
    
       'find position of the space in first address line
       iLocationStart = InStr(strLine1, " ")
       
       'take everything to the left of the space as ship_first
       ship_first = ""
       If iLocationStart > 1 Then
          ship_first = Left(strLine1, iLocationStart - 1)
       End If
       
       'take everything to the right of the space as ship_last
       ship_last = ""
       If iLocationStart < Len(strLine1) Then
          ship_last = Mid(strLine1, iLocationStart + 1)
       End If
    
       'address is whole line
       ship_address = strLine2
    
       'find position of first comma in third address line
       iLocationStart = InStr(strLine3, ",")
       
       'take everything to the left of the comma as ship_city
       ship_city = ""
       If iLocationStart > 1 Then
          ship_city = Left(strLine3, iLocationStart - 1)
       End If
       
       'find position of first space in third address line
       iLocationStart = InStr(strLine3, " ")
       ship_state = ""
       ship_zip = ""
       If iLocationStart < Len(strLine3) Then
       
          'find position of second space in third address line
          iLocationEnd = InStr(iLocationStart + 1, strLine3, " ")
          If iLocationEnd > 0 Then
          
             'take everything between the first two spaces as ship_state
             ship_state = Mid(strLine3, iLocationStart + 1, iLocationEnd - iLocationStart)
             If iLocationEnd < Len(strLine3) Then
                
                'take everything to the right of the second space as ship_zip
                ship_zip = Mid(strLine3, iLocationEnd + 1)
             End If
          End If
       End If
    
       '--- if you're going to loop through a recordset
       '--- the loop should end here
    
    
    End Sub

  3. #3
    Join Date
    Aug 2003
    Location
    Odenton, MD
    Posts
    2
    Wow! Thanks so much - I'll let you know how it works out, though I think it will be perfect!

    - Paul

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hope it goes well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •