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]

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!

I also tried modifying the code so that when the text string (e.g., "; text1") in a cell in Column I is split, there is no space in front of the split text (e.g., it should be "text1" instead of " text1"), but wasn't successful. Could this be done? Thanks!

 

[Peter_SSs]

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 ";"?

Is there a set number of columns?
- If so, how many?
- If not, how do we determine how many columns to use?

 

[keith11]

Is there a set number of columns?
- If so, how many?
- If not, how do we determine how many columns to use?

Good question! I was hoping it would be all available columns in Excel, if possible, otherwise up to Column AZ, or the last column with data. I don't have data beyond Column V currently but I was wondering if all columns for a row could be replicated, so I could use the Excel document as a great template in the future.

Any comments on the space before the split text (please refer to my previous post)? Thanks!

 

[Peter_SSs]

I was hoping it would be all available columns

We can do that. I was just trying to ensure there wasn't any more looping than necessary.

Any comments on the space before the split text (please refer to my previous post)? Thanks!

Yes, I was waiting for the previous answer to deal with both at once.

I'm afraid the code below does not have comments but much of it is similar to the previous code so hopefully you can figure out what is going on.

The leading (or trailing) spaces cause by spaces after/before a delimiter should be dealt with by the use of Trim(Bits(j)) - see about half way through the code.
I have taken the guesswork out of how many rows will be required for the Results array by doing all the splitting of the delimited data first and keeping a count of rows required while doing so.

Sub Rearrange_v3()
  Dim Data, Results, Bits, AllBits
  Dim nr As Long, rws As Long, i As Long, j As Long, k As Long, r As Long
  Dim lr As Long, lc As Long, sCol As Long, n As Long, BlockRws As Long
  Dim s As String
  
  Const HdrRow As Long = 4        '<-Header row
  Const SplitCol As String = "I"  '<- Col with delimiters
  Const Delim As String = ";"     '<- Your delimiter
  
  lr = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  lc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
  rws = lr - HdrRow + 1
  sCol = Columns(SplitCol).Column
  nr = 1
  ReDim AllBits(1 To 1)
  With Range("A" & HdrRow).Resize(rws, lc)
    Data = .Value
    For i = 1 To rws
      s = Data(i, sCol)
      If Len(s) Then
        Bits = Split(s, ";")
        r = UBound(Bits)
      Else
        Bits(0) = vbNullString
        r = 0
      End If
      ReDim Preserve AllBits(1 To nr + r)
      For j = 0 To r
        AllBits(nr + j) = Trim(Bits(j))
      Next j
      Data(i, sCol) = r + 1
      nr = nr + r + 1
    Next i
    ReDim Results(1 To nr - 1, 1 To lc)
    nr = 1
    For i = 1 To rws
      BlockRws = Data(i, sCol)
      For j = 0 To BlockRws - 1
        For k = 1 To lc
          Results(nr + j, k) = Data(i, k)
        Next k
      Next j
      nr = nr + BlockRws
    Next i
    .Resize(nr - 1).Value = Results
    .Offset(, sCol - 1).Resize(nr - 1, 1).Value = Application.Transpose(AllBits)
  End With
End Sub

 

[keith11]

Thanks a lot! The code ends in a runtime error on the line ".Offset(, sCol - 1).Resize(nr - 1, 1).Value = Application.Transpose(AllBits)" and results in everything (including the header) in column "I" being replaced with numbers such as 1, 2, 4, etc. It does not replicate any rows, too.

I have learned of a couple of new requirements over the past few days and wonder if the latest code can be modified so that it asks me which column's (currently the Column is "I") cells I need to manipulate and then it would replicate all rows for each of the split text, similar to what it's doing now for Column I.

In case the code cannot be modified to ask (this would be ideal) me about the column, I am looking to split content in columns G, H and J, but not at the same time. I would run the code to spilt text in Column I first, then for G, H and J. I appreciate your help very much! Your code has saved me a lot of time and sped up my work a lot. Thanks!

 

[Peter_SSs]

Thanks a lot! The code ends in a runtime error..

Did you give me the following?

When reporting an error, please quote the full error ..

on the line ".Offset(, sCol - 1).Resize(nr - 1, 1).Value = Application.Transpose(AllBits)"

Can you run the code on that data again and when the error occurs and you debug, hover the cursor over the yellow line ..
a) over the variable sCol and
b) over the variable nr and
.. report the values shown.

and results in everything (including the header) in column "I" being replaced with numbers such as 1, 2, 4, etc.

That's good, that is what should have happened at that point. It is the line you are reporting errors that should be writing in the delimited values over those numerical values.

It does not replicate any rows, too.

I don't understand why that is so but if we can resolve the above issues we may resolve that too.

I have learned of a couple of new requirements over the past few days and wonder if the latest code can be modified so that it asks me which column's (currently the Column is "I") cells I need to manipulate and then it would replicate all rows for each of the split text, similar to what it's doing now for Column I.

In case the code cannot be modified to ask (this would be ideal) me about the column, I am looking to split content in columns G, H and J, but not at the same time. I would run the code to spilt text in Column I first, then for G, H and J. I appreciate your help very much! Your code has saved me a lot of time and sped up my work a lot. Thanks!

Not much use trying to make such modifications if the original code is not working. Let's try to sort that out first.

Note that I will be away for a few days shortly, so don't be surprised if I do not reply for a while.

 

[keith11]

Did you give me the following?


Can you run the code on that data again and when the error occurs and you debug, hover the cursor over the yellow line ..
a) over the variable sCol and
b) over the variable nr and
.. report the values shown.


That's good, that is what should have happened at that point. It is the line you are reporting errors that should be writing in the delimited values over those numerical values.


I don't understand why that is so but if we can resolve the above issues we may resolve that too.

Not much use trying to make such modifications if the original code is not working. Let's try to sort that out first.

Note that I will be away for a few days shortly, so don't be surprised if I do not reply for a while.

Sorry! The error text is as follows: Run-time error "13": Type mismatch.

Debug result: a) sCol = 9, b) nr=232

 

[keith11]

Sorry! The error text is as follows: Run-time error "13": Type mismatch.

Debug result: a) sCol = 9, b) nr=232

The need for that macro has become more urgent in the past few days so I would appreciate if someone can comment on the code Peter has been helping me with, since he is not available. Thanks for the help in advance.

 

[Peter_SSs]

Sorry! The error text is as follows: Run-time error "13": Type mismatch.

Debug result: a) sCol = 9, b) nr=232

Those value don't look like they should be a problem and I have been unable to replicate your error on the line quoted:

The code ends in a runtime error on the line ".Offset(, sCol - 1).Resize(nr - 1, 1).Value = Application.Transpose(AllBits)"

 

[biksubejat]

Hi all,

Sorry to once again reliving an old thread!
I've got a similar case and I've got the logic somewhat right (I think), unfortunately I'm not the greatest VB programmer in the room!

So, the input data would be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cell A[/TD]
[TD]Cell B[/TD]
[/TR]
[TR]
[TD]s,m,l[/TD]
[TD]black,green,yellow[/TD]
[/TR]
</tbody>[/TABLE]



Ideally the output would be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cell A[/TD]
[TD]Cell B[/TD]
[/TR]
[TR]
[TD]s[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]s[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]s[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]l [/TD]
[TD]yellow[/TD]
[/TR]
</tbody>[/TABLE]



The logic that I am going for would be:

[COLOR=#333333][FONT=monospace]for (each itemA in col a) { 
   for (each itemB in col b){ 
      print itemA in Col A;
      print itemB in Col B;
      go to new line; 
    }
}
[/FONT][/COLOR]

I think I got the logic right? Wondering if any VB guru out there would be able to translate this into correct VB language?

Many thanks!