Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    This might be impossible, but here's the issue.

    I was handed 1-column Excel file has hundreds of addresses. Some addresses are 3 lines, some 4, some 5 or 6 lines.

    I need to separate these addresses so that each address is in a row (in order to do a mail merge, for example).

    Does anyone have a suggestion how to parse these since the addresses could take up a variable number of rows.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How does one determine where an address begins (or ends)? A sample workbook might help.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Since there are variable number of rows for each address, a macro will probably be needed. Please post a sample of the workbook (version 2003).

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That's exactly my issue. Some addresses occupy only 3 lines, some take 4, some take 5.

    While they all end in an American Zip code, some company names actually contain the zip code as part of their name.

    That's why I thought it was impossible since the issue is how to determine where one address block starts and stops, and the next one starts, etc.

    [quote name='HansV' post='793796' date='17-Sep-2009 18:58']How does one determine where an address begins (or ends)? A sample workbook might help.[/quote]

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are the zip codes 5 digits or plus 4s or a combination?
    Please post a sample workbook.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Zip as well as zip+4. It could look like the attached.

    [quote name='mbarron' post='793800' date='17-Sep-2009 19:10']Are the zip codes 5 digits or plus 4s or a combination?
    Please post a sample workbook.[/quote]
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See if this does what you want. Modify the names of the worksheets as needed.

    Code:
    Sub ConvertAddresses()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim s As Long
      Dim m As Long
      Dim t As Long
      Dim c As Long
      ' Modify sheet names as needed
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet2")
      wshTrg.Cells.ClearContents
      m = wshSrc.Cells(wshSrc.Rows.Count, 1).End(xlUp).Row
      t = 1
      c = 1
      For s = 1 To m
    	wshTrg.Cells(t, c) = Trim(wshSrc.Cells(s, 1))
    	If InStr(wshSrc.Cells(s, 1), ",") > 0 Then
    	  t = t + 1
    	  c = 1
    	Else
    	  c = c + 1
    	End If
      Next s
      wshTrg.Columns.AutoFit
    End Sub
    Inspect the result carefully. Some manual correction may be necessary.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    On the test file, it worked fine. I think your code looks for the comma. However, on the real file, it fails if there's a comma somewhere else other than after the city name. If a company name is XXX, Inc. if fails.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='kweaver' post='793816' date='18-Sep-2009 02:29']If a company name is XXX, Inc. if fails.[/quote]
    Yep. However, correcting that manually is probably less work and faster than trying to perfect the code.

    Of course, it would be even better to ask whoever gave you the workbook to produce a usable version. But I am aware that that is probably not a realistic option.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could try this If statement.
    Code:
    	If InStr(wshSrc.Cells(s, 1), ",") > 0 And _
    		IsNumeric(Mid(StrReverse(Trim(wshSrc.Cells(s, 1))), 4, 1)) Then
    It checks for the comma and the existence of a number four spot from the right as well as the comma.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Ah. Clever, clever, clever. Thanks.

    [quote name='mbarron' post='793819' date='17-Sep-2009 22:13']You could try this If statement.
    Code:
    	If InStr(wshSrc.Cells(s, 1), ",") > 0 And _
    		IsNumeric(Mid(StrReverse(Trim(wshSrc.Cells(s, 1))), 4, 1)) Then
    It checks for the comma and the existence of a number four spot from the right as well as the comma.[/quote]

Posting Permissions

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