Text To Row Function

[Phanidhar]

Hi,
I've a large data in excel rows. One of the column contains strings
separated with column values. For e.g.,

john 100 Ford,GM,Toyota NY
Bill 200 Jeep,Cadillac BOS
....................................
.........................

the column containing the car types shuold be split as multiple rows as
sjown belo

john 100 Ford NY
John 100 GM NY
john 100 Toyota NY
Bill 200 Jeep BOS
Bill 200 Cadillac BOS

Any help in getting this done would be highly appreciated.

Thanks,
Phani

 

[Gary''s Student]

This assumes that the input is on sheet s1 and the output is to sheet s2:

Sub phan()
Set s1 = Sheets("s1")
Set s2 = Sheets("s2")
n = s1.Cells(Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To n
s = Split(s1.Cells(i, 3).Value, ",")
ub = UBound(s)
For jj = 0 To ub
s2.Cells(j, 1).Value = s1.Cells(i, 1)
s2.Cells(j, 2).Value = s1.Cells(i, 2)
s2.Cells(j, 4).Value = s1.Cells(i, 4)
s2.Cells(j, 3).Value = s(jj)
j = j + 1
Next
Next
End Sub