VBA Code to split cell text by comma in new row

[Anaya Zeeshan]

I am trying to split text in a cell by comma. Each word should be in the new row. I have written this code and its working but its not pasting the text in new line instead its pasting in cell B2 only.
This is the code I have written:

Sub test_Comma()
Dim MyArray() As String
Dim N As Integer
Dim Rng1 As Range
Dim Cell As Range

'Setup Rng1
Set Rng1 = Range("A1", "A" & Cells(Rows.Count, 1).End(xlUp).Row)
'loop through each cell

For Each Cell In Rng1

'Split cells
MyArray = Split(Cell, ",")
'iterate through the array
For N = 0 To UBound(MyArray)
'Place each split into the first column of the worksheet
Range("B" & N + 1).Value = MyArray(N)


Next N

Next Cell
End Sub

 

[DanteAmor]

Try this:

Sub test_Comma()
  Dim n As Long
  Dim Cell As Range
  Dim itm As Variant
  For Each Cell In Range("A1", Range("A" & Rows.Count).End(3))
    For Each itm In Split(Cell, ",")    'Split cell
      n = n + 1
      Range("B" & n).Value = Trim(itm)
    Next itm
  Next Cell
End Sub

 

[Phuoc]

Try change:

Range("B" & N + 1).Value = MyArray(N)

to:

Cell.Offset(0, N + 1).Value = MyArray(N)

 

[Rick Rothstein]

Give this macro a try...

Sub Zeeshan_Comma_Macro()
  Dim Data As Variant
  Data = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ", "), ", ")
  Range("B1").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub

 

[alansidman]

In Power Query which is called Get and Transform Data in your version.

Split the column using the comma as a delimiter. Click on the option to split to rows instead of columns

 

[Anaya Zeeshan]

Try this:

Sub test_Comma()
  Dim n As Long
  Dim Cell As Range
  Dim itm As Variant
  For Each Cell In Range("A1", Range("A" & Rows.Count).End(3))
    For Each itm In Split(Cell, ",")    'Split cell
      n = n + 1
      Range("B" & n).Value = Trim(itm)
    Next itm
  Next Cell
End Sub

Thank you it worked
I want to paste the data in last available row of column B in sheet 2. I tried this but its not working
worksheets(2).Range("B" & n).Value = Trim(itm)

 

[DanteAmor]

Try this:

Sub test_Comma()
  Dim n As Long
  Dim Cell As Range
  Dim itm As Variant
  n = Sheets(2).Range("B" & Rows.Count).End(3).Row
  For Each Cell In Sheets(1).Range("A1", Sheets(1).Range("A" & Rows.Count).End(3))
    For Each itm In Split(Cell, ",")    'Split cells 'iterate through the array
      n = n + 1
      Sheets(2).Range("B" & n).Value = Trim(itm)
    Next itm
  Next Cell
End Sub

 

[Anaya Zeeshan]

Give this macro a try...

Sub Zeeshan_Comma_Macro()
  Dim Data As Variant
  Data = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ", "), ", ")
  Range("B1").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub

it worked, thanks