Excel macro -Split comma separated entries to new rows
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.
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
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"]
Dim objRegex As Object
Dim lngRow As Long
Dim lngCnt As Long
Dim tempArr() As String
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")
'Dump the re-ordered range to columns C:D
[c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y)
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
- 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?