Separating multiple data (w delimiter) from one cell to many cells

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Sub SplitAndReplicateData()
Dim rMyRng As Range, r As Range, rNxt As Range, i As Integer, vSplit As Variant

Set rMyRng = Application.InputBox("Select Data Range", "Range Req.", _
                Range("A1").CurrentRegion.Address, , , , , 8)

Sheets.Add
Set rNxt = ActiveCell

Application.ScreenUpdating = False

For Each r In rMyRng.Rows
    vSplit = Split(Replace(r.Cells(2).Value, """", ""), ",")
    For i = 0 To UBound(vSplit)
        r.Copy rNxt
        rNxt.Offset(, 1).Value = vSplit(i)
        Set rNxt = rNxt.Offset(1)
    Next i
Next r

Range("A1").CurrentRegion.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.