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!

 

[Joe4]

Welcome to the board!

You can use Data | Text to Columns and use the Comma to separate the data into different columns.

Then you can Copy the data and use Edit | Paste Special | Transpose to turn the rows into columns.

 

[VoG]

Try this with a copy of your sheet. Press ALT + F11 to open the Visual Basic Editor then Insert > Module. Paste in

Sub tst()
Dim X As Variant
X = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

then press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight tst and click Run. This assumes that your value is in A1 and you want it split to A1, A2, ...

 

[Bond007]

Thanks guys!

Perfecto

 

[CharterJace]

I know this thread is old, but this is so close to what I'm trying to pull off that I thought reviving it may be better for those who search than creating a separate thread.

In the example above, it appears that there is only a single cell and no columns with associated data to the right of column B. What if you needed to delimit multiple rows in column A and still keep the data in rows b, c, d, e, etc associate with the corresponding row in column A?

Example:

  A                    |B                    |C
1car, door             |mechanical           |auto
2fruit, apple          |plant                |tree
3pie, cherry           |dessert              |pastry

So that the outcome would look like this:

  A                    |B                    |C
1car                   |mechanical           |auto
2door                  |mechanical           |auto
3fruit                 |plant                |tree
4apple                 |plant                |tree
5pie                   |dessert              |pastry
6cherry                |dessert              |pastry

I'm not entirely sure that it's possible to do what I'm after, but I'm also not entirely sure that it isn't. So I figured I would ask the experts.

Thanks in advance.

 

[VoG]

This works with columns A to C, splitting the values in column A.

Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
For i = LR To 1 Step -1
    With Range("B" & i)
        If InStr(.Value, ",") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            X = Split(.Value, ",")
            .Offset(1).Resize(UBound(X)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        End If
    End With
Next i
Columns("B").Delete
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:C" & LR)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[CharterJace]

Thanks, I will give that a try later today and let you know what I experience. It is worth noting that the spreadsheet I am working with has data in columns A through V... I only used three rows for my sample. And it is only Column A that has the terribly structured data.

Will report back after I play with it some.

Thanks again.

 

[cduerson]

This works with columns A to C, splitting the values in column A.

Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
For i = LR To 1 Step -1
    With Range("B" & i)
        If InStr(.Value, ",") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            X = Split(.Value, ",")
            .Offset(1).Resize(UBound(X)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        End If
    End With
Next i
Columns("B").Delete
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:C" & LR)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Thanks for the post, this gives me a starting point.

I have one issue and one question.

The issue is that the code appears to break after 40 lines of data, at the point where the data gets shifited over 1 column it takes the first 40 lines and blows up with the following error. "Error -2147417848 (&H80010108): The object invoked has disconnected from its clients."

I found a reference to it on this message board but am not sure how to fix.
http://www.mrexcel.com/forum/showthread.php?t=11143, It says something about declaring Global Objects in the code.

I have hundreds of lines of data that I need to split data in so I need to fix this error. Any help you can give would be great.

My question is around my particular need for this code. I have a similar situation to the previous post however instead of 3 columns of data my data goes from A - Q and my comma separated field is in "M". I would like to repeat column A - L , and N-Q for each item from the split of the data in column M. My data is currently around 500 lines but could go longer.

The data is generated output so I would prefer to not manually manipulate the data before splitting it. I would like to take the generated report and put in the macro and have my data split. If you can point me to a starting point that would be great. I programmed many many years ago but am not familiar with VB.

Thank you in advance for your assistance

 

[VoG]

This splits column M. I've tested this with over 200 rows of data with no error (Excel 2007).

Sub Splt()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
LR = Range("M" & Rows.Count).End(xlUp).Row
Columns("M").Insert
For i = LR To 1 Step -1
    With Range("N" & i)
        If InStr(.Value, ",") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            X = Split(.Value, ",")
            .Offset(1).Resize(UBound(X)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        End If
    End With
Next i
Columns("N").Delete
LR = Range("M" & Rows.Count).End(xlUp).Row
With Range("A1:Q" & LR)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[CharterJace]

The code you originally provided me didn't work as it stood, and I'm simply not clever enough to modify it so that it will work with my added columns.

Is there a shortcut way to have column A split without specifying a limit to the number of columns which include data? So rather than terminating at C, D, or E it will work regardless?

Would I be better served to give the code provided to cduerson modifying the references to columns M and N to A and B respectively?