from to 3 rows to 12 rows (please macro or formula)

[Serdar Olgun]

I want to convert


A) is B) are C) am D) be
A) Your / surname B) My / surname C) I / surname D) I / name
A) My am B) I is C) I am D) I

TO

A) is
B) are
C) am
D) be
A) Your / surname
B) My / surname
C) I / surname
D) I / name
A) My am
B) I is
C) I am
D) I

 

[Serdar Olgun]

On Wednesday, September 5, 2012 10:23:24 AM UTC+3, Serdar Olgun wrote:
first please look at that pic : http://d1209.hizliresim.com/11/5/cnzh4.jpg

 

[Serdar Olgun]

solved thank you


Sub Convert()
Dim arr
Dim i As Long, EndR1 As Long, EndR2 As Long

With Sheets("Sayfa1")
EndR1 = .Range("A65000").End(xlUp).Row
For i = 1 To EndR1
arr = Split(.Range("A" & i).Value, " B)")
EndR2 = .Range("C65000").End(xlUp).Row
If EndR2 = 1 Then EndR2 = 0
..Range("C" & EndR2 + 1).Value = arr(0)
arr = Split(arr(1), " C)")
..Range("C" & EndR2 + 2).Value = "B)" & arr(0)
arr = Split(arr(1), " D)")
..Range("C" & EndR2 + 3).Value = "C)" & arr(0)
..Range("C" & EndR2 + 4).Value = "D)" & arr(1)
Next i
End With
End Sub