Excel macro -Split comma separated entries to new rows

add comma separated values in excel
excel formula comma separated values
excel comma separate column
how to export excel data to comma separated text
excel row to string
copy and paste from excel with commas
delimited list in excel
how to add comma in excel column

I currently have this data in a sheet

Col A   Col B   Col C
1       A       angry birds, gaming
2       B       nirvana,rock,band

What I want to do is split the comma separated entries in the third column and insert in new rows like below:

Col A   Col B   Col C
1       A       angry birds
1       A       gaming
2       B       nirvana
2       B       rock
2       B       band

I am sure this can be done with VBA but couldn't figure it out myself.

variant using Scripting.Dictionary

Sub ttt()
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    Dim x&, cl As Range, rng As Range, k, s
    Set rng = Range([C1], Cells(Rows.Count, "C").End(xlUp))
    x = 1 'used as a key for dictionary and as row number for output
    For Each cl In rng
        For Each s In Split(cl.Value2, ",")
            dic.Add x, Cells(cl.Row, "A").Value2 & "|" & _
                        Cells(cl.Row, "B").Value2 & "|" & LTrim(s)
            x = x + 1
    Next s, cl
    For Each k In dic
        Range(Cells(k, "A"), Cells(k, "C")).Value2 = Split(dic(k), "|")
    Next k
End Sub

source:

result:

I need to convert values in Column to comma separated values in a , I need to convert values in Column to comma separated values in a single cell using a VBA macro in excel. What I want to do is split the comma separated entries in the second column and insert in new rows like below: Col A Col B 1 angry birds 1 gaming 2 nirvana 2 rock 2 band I am sure this can be done with VBA but couldn't figure it out myself.

If you have a substantial amount of data, you willfind working with arrays beneficial.

Sub Macro2()
    Dim i As Long, j As Long, rws As Long
    Dim inp As Variant, outp As Variant

    With Worksheets("sheet2")
        inp = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "C").End(xlUp)).Value2

        For i = LBound(inp, 1) To UBound(inp, 1)
            rws = rws + UBound(Split(inp(i, 3), ",")) + 1
        Next i

        ReDim outp(1 To rws, 1 To 3)
        rws = 0

        For i = LBound(inp, 1) To UBound(inp, 1)
            For j = 0 To UBound(Split(inp(i, 3), ","))
                rws = rws + 1
                outp(rws, 1) = inp(i, 1)
                outp(rws, 2) = inp(i, 2)
                outp(rws, 3) = Trim(Split(inp(i, 3), ",")(j))
            Next j
        Next i

        .Cells(1, "A").Resize(UBound(outp, 1), UBound(outp, 2)) = outp

    End With
End Sub

Convert Column to Comma Separated List in Excel, Or how to convert a column into comma delimited list in Excel. and you want to convert all values in column B to comma separated list in another You can also use an Excel VBA macro to quickly convert column to a comma separated list in Excel. #3 click “Insert” ->”Module” to create a new module. I currently have this data in a sheet: Col A Col B 105 399, 400 207 405,406,407 and want to split the comma-separated entries in the second column and insert them in new rows as below:

This is the answer I have for a two column data. But I want to do it for three columns, Can someone help me here?

You are better off using variant arrays rather than cell loops - they are much quicker code wise once the data sets are meaningful. Even thoug the code is longer :)

This sample below dumps to column C and D so that you can see the orginal data. Change [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) to [a1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) to dump over your original data

[Updated with regexp to remove any blanks after , ie ", band" becomes "band"] Sub SliceNDice() Dim objRegex As Object Dim X Dim Y Dim lngRow As Long Dim lngCnt As Long Dim tempArr() As String Dim strArr Set objRegex = CreateObject("vbscript.regexp") objRegex.Pattern = "^\s+(.+?)$" 'Define the range to be analysed X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2 Redim Y(1 To 2, 1 To 1000) For lngRow = 1 To UBound(X, 1) 'Split each string by "," tempArr = Split(X(lngRow, 2), ",") For Each strArr In tempArr lngCnt = lngCnt + 1 'Add another 1000 records to resorted array every 1000 records If lngCnt Mod 1000 = 0 Then Redim Preserve Y(1 To 2, 1 To lngCnt + 1000) Y(1, lngCnt) = X(lngRow, 1) Y(2, lngCnt) = objRegex.Replace(strArr, "$1") Next Next lngRow 'Dump the re-ordered range to columns C:D [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) End Sub

Excel Macro, If you want to keep your original data and merely summarize the data somewhere else, you can use the following method. Create a  In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant. Here is an example of what I would like to do through VBA: 211817 to

This is not a polished solution, but I need to spend some time with the wife.

But still another way of thinking about it.

This code assumes that the sheet is called Sheet4 and the range that needs to be split is col C.

Dim lastrow As Integer
Dim i As Integer
Dim descriptions() As String

With Worksheets("Sheet4")
    lastrow = .Range("C1").End(xlDown).Row
    For i = lastrow To 2 Step -1
        If InStr(1, .Range("C" & i).Value, ",") <> 0 Then
            descriptions = Split(.Range("C" & i).Value, ",")
        End If
        For Each Item In descriptions
            .Range("C" & i).Value = Item
            .Rows(i).Copy
            .Rows(i).Insert
        Next Item
        .Rows(i).EntireRow.Delete

    Next i
End With

Parse multiple values in a cell into separate rows, if you really want those new rows to have blank cells in the other for a macro that parses a cell with multiple values (separated by a comma)  Split comma separated values into rows with VBA code. For splitting comma separated values into rows, you can apply the following VBA code. 1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window. 2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste

This will do what you want.

Option Explicit

Const ANALYSIS_ROW As String = "C"
Const DATA_START_ROW As Long = 1

Sub ReplicateData()
    Dim iRow As Long
    Dim lastrow As Long
    Dim ws As Worksheet
    Dim iSplit() As String
    Dim iIndex As Long
    Dim iSize As Long

    'Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ThisWorkbook
        .Worksheets("Sheet1").Copy After:=.Worksheets("Sheet1")
        Set ws = ActiveSheet
    End With

    With ws
        lastrow = .Cells(.Rows.Count, ANALYSIS_ROW).End(xlUp).Row
    End With


    For iRow = lastrow To DATA_START_ROW Step -1
        iSplit = Split(ws.Cells(iRow, ANALYSIS_ROW).Value2, ",")
        iSize = UBound(iSplit) - LBound(iSplit) + 1
        If iSize = 1 Then GoTo Continue

        ws.Rows(iRow).Copy
        ws.Rows(iRow).Resize(iSize - 1).Insert
        For iIndex = LBound(iSplit) To UBound(iSplit)
            ws.Cells(iRow, ANALYSIS_ROW).Offset(iIndex).Value2 = iSplit(iIndex)
        Next iIndex
Continue:
    Next iRow

    Application.CutCopyMode = False
    Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub

Group set of values from a column to a comma separated row with a , I have a few hundred rows with only values in column A. I want to get groups of this a comma separated string on every multiple of 10 row in column B (row 1,10,​21 etc). I DON'T want to use a Macro, just a worksheet function. I tried your new formula, but it gives me unexpected results, such as fewer  This post will guide you how to separate comma delimited cells to new rows or columns in Excel. How do I split comma separated values into rows with Text to Column Feature in Excel. How to convert comma separated text into rows or columns with VBA Macro in Excel. Split Comma Separated Values into Rows read more »

Excel Macros to Work With Text, Enhanced CONCATENATE function, delete workbook rows if specific words found or omitted. from Excel MVP Rick Rothstein, concatenates a range of cells​, and the elements (think comma separated listing as one possible use). (​Road Design) for the New Jersey Department of Transportation. All of the data from columns A, B, D, and E would be repeated in the new rows. In other words, rather than simply having each order take up a row, each product ordered would get its own row. Text to Columns would let you easily split column C into new columns, but transposing those new columns to rows would probably require a VBA macro.

Convert A Column Into Comma Delimited List, Back once again with a new problem. Do this 10-15 times to create that many comma delimited lists then concatenate If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done Put this in the cell to grab the first 16 cells of column I:. What I came up with is a very simple method of Converting Comma Separated Text Into Rows using Microsoft Excel. I even made another How-to Video Tutorial showing you each and every step found below. As always send me your questions and requests through he commenting section on my blog or YouTube videos.

How To Concatenate A Range With A Delimiter, VBA | 2 |. There are two ways of concatenating cells in Excel, either with the built-in This is a good example where creating a short user defined function in VBA could be a big help. In this example let's concatenate column 1 and have the list separated by commas. Notify me of new posts by email. I have no acknowledge on VBA and programming and I currently have this data in a sheet Col A Col B 105 399, 400 207 405,406,407 What I want to do is split the comma separated entries in the second column and insert in new rows like below: Col A Col B 105 399

Comments
  • Hello and welcome to StackOverflow. Please take some time to read the help page, especially the sections named "What topics can I ask about here?" and "What types of questions should I avoid asking?". And more importantly, please read the Stack Overflow question checklist. You might also want to learn about MCVE. And include the code you are trying to work through...so people can help.
  • This did do what I wanted exactly. Could you explain a bit on how it works?