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

**rocketeer425** · 02-11-2014, 04:51 AM

Hi Sixthsense,

Please see the sample workbook attached in this post.

Additional information you may need:

I need the "Account #" column to be entered separately per row. Each account # for each row.

All other columns for a specific account # should also be included.

The comma (,) delimiter should be removed and the quote-sign (" ")

Thank you

**:) Sixthsense :)** · 02-11-2014, 06:16 AM

How to install your new code

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

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
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