How seperate text with comma in a cell into seperate rows

[geniusideas]

I wanted to seperate text with comma in a cell into different row for
example

Before :

Capasitor 3 C123,C124,C125
Diode 2 D110,111
Transistor 3 T101,T102,T103

After

Capasitor 1 C123
Capasitor 1 C124
Capasitor 1 C125
Diode 1 D110
Diode 1 D111
Transistor 1 T101
Transistor 1 T102
Transistor 1 T103

How to create Excel VBA ? Need urgently...pls help guy

 

[Per Jessen]

Hi

With before data in columns A:C and "after" data in columns D:F, try
this.



Sub seperate()
firstRow = 2
LastRow = Range("A" & Rows.Count).End(xlUp).Row
rw = 2
For r = firstRow To LastRow
Comp = Range("A" & r).Value
txt = Split(Range("C" & r).Value, ",")
For c = 0 To UBound(txt)
Range("D" & rw) = Comp
Range("E" & rw) = 1
Range("F" & rw) = txt(c)
rw = rw + 1
Next
Next
End Sub

Hopes this helps.

 

[Rick Rothstein]

We need a little more information. One, where did you want the expanded data
to go... in another column or did you want it to replace the original data?
Two, what if there were repeated codes from in a row; for example...

Capasitor 3 C123,C124,C124

did you want each C124 in its own row or did you want them summed together
on a single line, like this...

Capasitor 1 C123
Capasitor 2 C124

 

[geniusideas]

Hi

With before data in columns A:C and "after" data in columns D:F, try
this.

Sub seperate()
firstRow = 2
LastRow = Range("A" & Rows.Count).End(xlUp).Row
rw = 2
For r = firstRow To LastRow
    Comp = Range("A" & r).Value
    txt = Split(Range("C" & r).Value, ",")
    For c = 0 To UBound(txt)
        Range("D" & rw) = Comp
        Range("E" & rw) = 1
        Range("F" & rw) = txt(c)
        rw = rw + 1
    Next
Next
End Sub

Hopes this helps.

Tq for code, it's work. the only thing I need to remove the original
list meaning new list will be in same column.Your vb code create a
list in another column.