Convert text to column

[mrcris]

Hi guys, i want to conver the data on A1 to separate question and answer as expectation. Could you help me to resolve it? I have many record like A1, i want to automatic. Thanks.

	A	B	C	D	E
1	1. Which of the following applications/software can be used to create a favicon? a) Unity b) Adobe Photoshop c) Dreamweaver d) VLC View Answer <tbody> </tbody>
2
3	Expected: <tbody> </tbody>
4
5	Which of the following applications/software can be used to create a favicon? <tbody> </tbody>	Unity <tbody> </tbody>	Adobe Photoshop <tbody> </tbody>	Dreamweaver <tbody> </tbody>	VLC <tbody> </tbody>

<tbody>
</tbody>

 

[BENNASR]

i am not sure that what u want
but try with this :

Sub test()    Dim Cell As Range
    Dim Plage As Range
    Set Plage = Range("A2:A100") ' à adapter
    For Each Cell In Plage
    Cells(Cell.Row, 2) = Replace(Cell.Value, Chr(10), "")
    Next
    
    Dim Tableau
    Dim i As Integer
    Dim j As Integer
    Dim L As Long
    With Sheets("Feuil1")
    L = .Range("B" & Rows.Count).End(xlUp).Row
    Z = 2
    For j = 2 To L
    Tableau = Split(.Cells(j, 1), "a)")
     .Cells(j, 2) = Tableau(0)
    Next j
    End With
    
    
End Sub

 

[MARK858]

Maybe something like....

Sub mrcris()
    Columns("A:A").TextToColumns Destination:=Range("B5"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, Other:=True, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True
End Sub

 

[mrcris]

I tried with alt + f11 and paste the code, how to apply to the excel? I didn't know, thanks guys for support me.

 

[MARK858]

Put the cursor within the code and click F5 or click Run - Run Sub/Userform or from the worksheet ALT+F8, click the name of the macro and click run.
.