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

Bond007

New Member
Joined
Dec 1, 2008
Messages
2
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
Try this with a copy of your sheet. Press ALT + F11 to open the Visual Basic Editor then Insert > Module. Paste in

Code:
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, ...
 
Upvote 0
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:

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


So that the outcome would look like this:

Code:
  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.
 
Last edited:
Upvote 0
This works with columns A to C, splitting the values in column A.

Code:
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
 
Upvote 0
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.
 
Upvote 0
This works with columns A to C, splitting the values in column A.

Code:
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
 
Upvote 0
This splits column M. I've tested this with over 200 rows of data with no error (Excel 2007).

Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top