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

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.

---
Per

On 8 Maj, 16:57, geniusideas wrote:
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

On May 8, 11:24*pm, Per Jessen wrote:
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.

---
Per

On 8 Maj, 16:57, geniusideas wrote:

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

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.

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

--
Rick (MVP - Excel)


"geniusideas" wrote in message
...
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