EXCEL VBA - Convert comma separated values to different columns

Question

I have data like this

  A  |   B   |   C
-----|-------|-------
  1  |  ABC  | L,F,M
  2  |  PQR  | G,D,S

Required

  A  |   B   |  C
-----|-------|----- 
  1  |  ABC  |  L
  1  |  ABC  |  F
  1  |  ABC  |  M
  2  |  PQR  |  G
  2  |  PQR  |  D
  2  |  PQR  |  S

Thanks in advance, for the help.

Answer 1

Try this

Sub SplitData()
    Dim cArray As Variant
    Dim cValue As String
    Dim rowIndex As Integer, strIndex As Integer, destRow As Integer
    Dim targetColumn As Integer
    Dim lastRow As Long
    Dim ws As Worksheet

    targetColumn = 3 'column number with comma separated data
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to your data data

    destRow = 0
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For rowIndex = 1 To lastRow
            cValue = .Cells(rowIndex, targetColumn).Value 'getting the cell with comma separated data
            cArray = Split(cValue, ",") 'splitting comma separated data in an array
            For strIndex = 0 To UBound(cArray)
                destRow = destRow + 1
                .Cells(destRow, 5) = .Cells(rowIndex, 1)    '5 represents Column E
                .Cells(destRow, 6) = .Cells(rowIndex, 2)    '6 represents Column F
                .Cells(destRow, 7) = Trim(cArray(strIndex)) '7 represents Column G
            Next strIndex
        Next rowIndex
    End With
End Sub