How to split single text cell into multiple rows, using a comma delimiter?
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 10 123 ... LastLast
Results 1 to 10 of 99

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

  1. #1
    New Member
    Join Date
    Dec 2008
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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!

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,439
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

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

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

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

    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, ...

  4. #4
    New Member
    Join Date
    Dec 2008
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks guys!

    Perfecto

  5. #5
    New Member
    Join Date
    Dec 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by CharterJace; Dec 18th, 2009 at 01:44 PM.

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

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

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    Dec 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  8. #8
    New Member
    Join Date
    Jan 2007
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by VoG View Post
    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

  9. #9
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

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

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  10. #10
    New Member
    Join Date
    Dec 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    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?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com