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

Cross posted:
http://www.excelforum.com/excel-prog...r-formula.html

Il 05/09/2012 09:32, Serdar Olgun ha scritto:
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



Sub sep()
Dim ColData As Long, RowBeg As Long, RowEnd As Long, mat()
Dim Items As Long, i As Long, j As Long
Dim Sfrom As Long, Snum As Long
ColData = 1
RowBeg = 1
RowEnd = 3
Items = 4 * (RowEnd - RowBeg + 1)
ReDim mat(1 To Items, 1 To 1)
For i = RowBeg To RowEnd
For j = 1 To 4
Sfrom = InStr(1, Cells(i, ColData), Chr(64 + j) & ")") + 3
Snum = InStr(1, Cells(i, ColData), Chr(65 + j) & ")") - Sfrom - 1
If Snum < 0 Then Snum = 999
mat((i - RowBeg) * 4 + j, 1) = Mid(Cells(i, ColData), Sfrom, Snum)
Next j
Next i
Cells(RowBeg, ColData + 1).Resize(Items).Value = mat
End Sub

Set correct values for ColData, RowBeg and RowEnd
Hi,
E.

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