Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Thanked 0 Times in 0 Posts

    Converting single comma-separated row into multiple rows (Access 2010)

    If anyone could provide advice on how to solve my dilemma, that would be terrific.

    I have a table in Access 2010 that contains a comma delimited list of zip codes in a single cell by company#. This list is provided to my database from a different system and the list is unable to be modified. I would like to create a new table with the list where the single comma separated rows are converted into multiple rows with 1 zip code for each company#.

    Attached is this post is a sample of the data I am using. The table is called tblListingsAndZips.

    I would like to keep my original table and create a new table with a single zip code listed for each company#.

    Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,454 Times in 1,323 Posts

    If this is what you're after:
    Then this code:
    Option Explicit
    Sub ProcessZips()
       Dim vRawZips  As Variant
       Dim iZipCnt   As Integer
       Dim iCntr     As Integer
       Dim lDestRow  As Integer
       Dim zCCode    As String
       Dim zDestSht  As String
       Application.ScreenUpdating = False
       lDestRow = 2
       zDestSht = "NewList"
       Sheets(zDestSht).Cells(1, 1).Value = "Company#"
       Sheets(zDestSht).Cells(1, 2).Value = "ZipCode"
         ActiveCell.Offset(1, 0).Select
         vRawZips = Split(ActiveCell.Offset(0, -1).Value, ",")
         zCCode = ActiveCell.Value
         iZipCnt = UBound(vRawZips) - 1  '*** Array zero based!
         For iCntr = 0 To iZipCnt
            Sheets(zDestSht).Cells(lDestRow, 1).Value = zCCode
            Sheets(zDestSht).Cells(lDestRow, 2).Value = vRawZips(iCntr)
            lDestRow = lDestRow + 1
         Next iCntr
       Loop Until ActiveCell.Value = ""
    End Sub   'Process Zips
    Will accomplish the task in Excel and you can then import the table into Access. HTH
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

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