Split comma-separated values in one cell into columns with one value 1

[Bobo72]

Hi,

I have an xls spreadsheet that contains 7 columns of which 2 contains up to 8-10 comma-sep. values in each cell.

The data needs to be imported into an Oracle database. Before I can do that, I need to split the multiple values into new rows, in which the values in all other columns are copied to the new rows.

An example:
Itemnum Text Symbol Quantity
---------------------------------------------
123, 445 Description T, N 120

... should become

123 Description T 120
123 Description N 120
445 Description T 120
445 Description N 120

Any help would be very much appreciated. I guess that I need to build a new macro to do this? Best of all would be some help with VB-code - commented code even better so that I can understand what's going on : )

Best regards and thanks,
Bo

 

[PHV]

In the VBA help have a look at the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WinblowsME]

Sub Test()
   Dim i As Long, last_row As Long, j As Integer
   Dim item_num As String, desc As String, symbol As String, quantity As String
   Dim items() As String, symbols() As String, curr_item As Variant, curr_symbol As Variant
   
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

   For i = last_row To 1 Step -1
      item_num = Cells(i, 1)
      desc = Cells(i, 2)
      symbol = Cells(i, 3)
      quantity = Cells(i, 4)
      
      items = Split(item_num, ",")
      symbols = Split(symbol, ",")
      
      For j = 1 To (UBound(items) + 1) * (UBound(symbols) + 1) - 1
         Rows(i & ":" & i).Insert Shift:=xlDown
      Next j
      
      j = i
      
      For Each curr_item In items
         For Each curr_symbol In symbols
            Cells(j, 1) = Trim(curr_item)
            Cells(j, 2) = desc
            Cells(j, 3) = Trim(curr_symbol)
            Cells(j, 4) = quantity
            
            j = j + 1
         Next
      Next
   Next i
End Sub

 

[WinblowsME]

Dim j As Long.

 

[Bobo72]

Hi WinblowsME,

Thank you for the subroutine which I'm already using : )

/Bo