How to split single text cell into multiple rows, using a comma delimiter?

[Bond007]

Hello - could anyone help me? I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:

A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...

What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with

A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.

Many Thanks!

 

[keith11]

I have an article in my mini-blog site that can do what you ask. Here is the link...

Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

You will have to change the assignments being made in these three Const statements...

Const Delimiter As String = ", "
Const DelimitedColumn As String = "C"
Const TableColumns As String = "A:C"

to this for the data you posted...

Const Delimiter As String = ","
Const DelimitedColumn As String = "D"
Const TableColumns As String = "A:D"

Thanks, Rick. As I stated in my response to Peter, one post ago, my columns are not continuous. For my requirement, I modified your code as below:

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = ", "
  Const DelimitedColumn As String = "I"
  Const TableColumns As String = "A:I"
  Const StartRow As Long = 5

It gives me a runtime error #1004: application-defined or object-defined error. Any idea what I am missing? please refer to my previous post for further details.

 

[Rick Rothstein]

Thanks, Rick. As I stated in my response to Peter, one post ago, my columns are not continuous. For my requirement, I modified your code as below:

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = ", "
  Const DelimitedColumn As String = "I"
  Const TableColumns As String = "A:I"
  Const StartRow As Long = 5

It gives me a runtime error #1004: application-defined or object-defined error. Any idea what I am missing? please refer to my previous post for further details.

On what line is the error occurring? Also, is your delimiter a comma/space (like the above code shows) or just a comma by itself (like you original message shows in Message #47)?

 

[Peter_SSs]

.. the code threw an error #13.

When reporting an error, please quote the full error and identify what line of code is causing the error. As it happens I think that error is occurring because of blank cells in the 'delimited column' and I did note in my previous post that my code was based on the assumption that there were no blanks in that column - for that very reason.

Also, some cells in column I are blank.

You haven't said what to do with the row if column I is blank.

If a row that is blank in column I is to be excluded from the results, try this code.

Sub Rearrange_v1()
  Dim Data, Results, Bits
  Dim nr As Long, rws As Long, i As Long, j As Long, k As Long, r As Long
  Dim s As String
  
  Const HdrRow As Long = 4  '<-Header row
  Const IMax As Long = 10   '<-Max number of items expected in a single cell in col I
  
  With Range("A" & HdrRow, Range("A" & Rows.Count).End(xlUp)).Resize(, 9)
    Data = .Value
    rws = UBound(Data, 1)
    ReDim Results(1 To rws * IMax + 1, 1 To 9)
    nr = 1
    For i = 1 To rws
      s = Data(i, 9)
      If Len(s) Then
        Bits = Split(Replace(s, ".", ",", 1, -1, 1), ",")
        r = UBound(Bits)
        For j = 0 To r
          For k = 1 To 8
            Results(nr + j, k) = Data(i, k)
          Next k
          Results(nr + j, 9) = Bits(j)
        Next j
        nr = nr + r + 1
      End If
    Next i
    .Offset(, 10).Resize(nr - 1).Value = Results
  End With

End Sub

If a row that is blank in column I is to be included in the results, try this code.

Sub Rearrange_v2()
  Dim Data, Results, Bits
  Dim nr As Long, rws As Long, i As Long, j As Long, k As Long, r As Long
  Dim s As String
  
  Const HdrRow As Long = 4  '<-Header row
  Const IMax As Long = 10   '<-Max number of items expected in a single cell in col I
  
  With Range("A" & HdrRow, Range("A" & Rows.Count).End(xlUp)).Resize(, 9)
    Data = .Value
    rws = UBound(Data, 1)
    ReDim Results(1 To rws * IMax + 1, 1 To 9)
    nr = 1
    For i = 1 To rws
      s = Data(i, 9)
      If Len(s) Then
        Bits = Split(Replace(s, ".", ",", 1, -1, 1), ",")
        r = UBound(Bits)
      Else
        Bits(0) = vbNullString
        r = 0
      End If
      For j = 0 To r
        For k = 1 To 8
          Results(nr + j, k) = Data(i, k)
        Next k
        Results(nr + j, 9) = Bits(j)
      Next j
      nr = nr + r + 1
    Next i
    .Resize(nr - 1).Value = Results
  End With

End Sub

Both codes should not be bothered whether the column I values are delimited by "," or "." or a combination of both.

 

[keith11]

On what line is the error occurring? Also, is your delimiter a comma/space (like the above code shows) or just a comma by itself (like you original message shows in Message #47)?

Thanks, Rick. The error message doesn't say the line number. The error prompt says only this: Runtime error #1004: Application-defined or object-defined error.

I have comma and space, commas without space, periods, and blanks in the delimited column. Any further ideas?

 

[Peter_SSs]

Any further ideas?

Yes, post #53.

 

[keith11]

Yes, post #53.

Thanks! The code works. Is there a quick guide to the commands you have used in your code (with column I having blank cells), so I am able to modify it when needed?

It would also help immensely if you could explain the general logic of the code briefly, as in what the code essentially does. Thanks.

 

[Peter_SSs]

The code works. ..... (with column I having blank cells)

Which code?
Both codes deal with blanks in column I. The difference is how the code treats those rows that have blanks in column I. Re-read what I wrote about the codes and/or run both codes over a very small sample, say 3 rows of data, where 2 rows have something in column I and 1 row does not and look at the different results.

Is there a quick guide to the commands you have used in your code ...., so I am able to modify it when needed?

Not that I am aware of. You could look in the built-in vba Help.

It would also help immensely if you could explain the general logic of the code briefly, as in what the code essentially does. Thanks.

That should be possible once I know which code you are wanting to use.

 

[keith11]

Which code?
Both codes deal with blanks in column I. The difference is how the code treats those rows that have blanks in column I. Re-read what I wrote about the codes and/or run both codes over a very small sample, say 3 rows of data, where 2 rows have something in column I and 1 row does not and look at the different results.



Not that I am aware of. You could look in the built-in vba Help.



That should be possible once I know which code you are wanting to use.

Thanks, Peter. I am using the code (#2) which includes blank rows in Column I in the results.

By the logic of the code, I meant, the general flow of the code, e.g., the first part of the code does this, the second part of the code does this, etc.

I am looking to modify the code for ";" instead of commas - so that text which is separated by semi-colons is split by the code. What would I need to change? Thanks for your help!

 

[Peter_SSs]

Thanks, Peter. I am using the code (#2) which includes blank rows in Column I in the results.

By the logic of the code, I meant, the general flow of the code, e.g., the first part of the code does this, the second part of the code does this, etc.

I am looking to modify the code for ";" instead of commas - so that text which is separated by semi-colons is split by the code. What would I need to change? Thanks for your help!

OK, I have commented the v2 code below, but add the following points.

1. My original comment remains on the Const IMax line Max number of items expected in a single cell in col I
That comment is not worded quite how I meant. It should be more like Average number of items expected in a single cell in col I
But it should err on the high side. The purpose of that value is to ensure the 'Results' array is dimensioned large enough to hold all the rows expected to be produced.

2. The code was originally written to split column I values that were delimited by commas (,) periods (.) or a combination of both.
Examples
a,b,c
a.b.c
a,b.c

If you only need to split col I on a single delimiter then the red line of code below can be replaced with the following line where the blue part is the delimiter used in col I

Bits = Split(s, ";")

Sub Rearrange_v2()
  Dim Data, Results, Bits
  Dim nr As Long, rws As Long, i As Long, j As Long, k As Long, r As Long
  Dim s As String
  
  Const HdrRow As Long = 4  '<-Header row
  Const IMax As Long = 10   '<-Max number of items expected in a single cell in col I
  
  'Use range from col A header row to last data in col A & expand to col I
  With Range("A" & HdrRow, Range("A" & Rows.Count).End(xlUp)).Resize(, 9)
    'Read all the values into an array in memory
    Data = .Value
    'Calculate how many rows of data by how many rows in the array
    rws = UBound(Data, 1)
    'Make a new array, big enough to hold the max expected results
    ReDim Results(1 To rws * IMax + 1, 1 To 9)
    'Start a row counter for the Results array
    nr = 1
    'Work through each row of the array/data
    For i = 1 To rws
      'Put the col I value into a string variable
      s = Data(i, 9)
      'If there is a string to deal with (len > 0)
      If Len(s) Then
        'Split the string into bits (see further notes in my post)
        Bits = Split(Replace(s, ".", ",", 1, -1, 1), ",")
        'r is 1 less than the number of bits (bits are numbered from 0, 1, 2, ..)
        r = UBound(Bits)
        
      'Col I cell is empty if we get to process this section
      Else
        Bits(0) = vbNullString
        r = 0
      End If
      'For each 'Bit'
      For j = 0 To r
        'Put original data from cols A:H into Results array ..
        For k = 1 To 8
          Results(nr + j, k) = Data(i, k)
        Next k
        '.. and the next 'Bit' into the 9th col of the Results array
        Results(nr + j, 9) = Bits(j)
      Next j
      'Calculate what row in the Results array the next section will start
      nr = nr + r + 1
    Next i
    
    'Write the Results array back to the worksheet
    .Resize(nr - 1).Value = Results
  End With

End Sub

 

[keith11]

OK, I have commented the v2 code below, but add the following points.

1. My original comment remains on the Const IMax line Max number of items expected in a single cell in col I
That comment is not worded quite how I meant. It should be more like Average number of items expected in a single cell in col I
But it should err on the high side. The purpose of that value is to ensure the 'Results' array is dimensioned large enough to hold all the rows expected to be produced.

2. The code was originally written to split column I values that were delimited by commas (,) periods (.) or a combination of both.
Examples
a,b,c
a.b.c
a,b.c

If you only need to split col I on a single delimiter then the red line of code below can be replaced with the following line where the blue part is the delimiter used in col I

Bits = Split(s, ";")

Sub Rearrange_v2()
  Dim Data, Results, Bits
  Dim nr As Long, rws As Long, i As Long, j As Long, k As Long, r As Long
  Dim s As String
  
  Const HdrRow As Long = 4  '<-Header row
  Const IMax As Long = 10   '<-Max number of items expected in a single cell in col I
  
  'Use range from col A header row to last data in col A & expand to col I
  With Range("A" & HdrRow, Range("A" & Rows.Count).End(xlUp)).Resize(, 9)
    'Read all the values into an array in memory
    Data = .Value
    'Calculate how many rows of data by how many rows in the array
    rws = UBound(Data, 1)
    'Make a new array, big enough to hold the max expected results
    ReDim Results(1 To rws * IMax + 1, 1 To 9)
    'Start a row counter for the Results array
    nr = 1
    'Work through each row of the array/data
    For i = 1 To rws
      'Put the col I value into a string variable
      s = Data(i, 9)
      'If there is a string to deal with (len > 0)
      If Len(s) Then
        'Split the string into bits (see further notes in my post)
        Bits = Split(Replace(s, ".", ",", 1, -1, 1), ",")
        'r is 1 less than the number of bits (bits are numbered from 0, 1, 2, ..)
        r = UBound(Bits)
        
      'Col I cell is empty if we get to process this section
      Else
        Bits(0) = vbNullString
        r = 0
      End If
      'For each 'Bit'
      For j = 0 To r
        'Put original data from cols A:H into Results array ..
        For k = 1 To 8
          Results(nr + j, k) = Data(i, k)
        Next k
        '.. and the next 'Bit' into the 9th col of the Results array
        Results(nr + j, 9) = Bits(j)
      Next j
      'Calculate what row in the Results array the next section will start
      nr = nr + r + 1
    Next i
    
    'Write the Results array back to the worksheet
    .Resize(nr - 1).Value = Results
  End With

End Sub

The code with the single delimiter ";" works!! You are awesome, Peter! Thanks for the painful task of adding comments to your code, too - it is very helpful.

I noticed that the code replicates the content of all the columns in the same row leading up to column I, but not the columns after I. Could the code me modified so that the whole original row is replicated and added as new rows when the text in a cell in Column I is split using the delimiter ";"? Thanks for your time and expertise!