Text to Row by Delimiter

[betty77]

I want to import a text file into excel. Instead of divide the text into
columns by delimiter, I want to divide the text into rows by delimiter.
Can anyone tell me how to do this? Thanks.

 

[Tom Ogilvy]

Possibly divide it by column, then select the data and do edit => copy, then
edit => Paste Special => transpose

and delete the residual initial data.

or write code to parse it and place it yourself.

 

[betty77]

Thanks for your help. I first used text to column then I tried to use
small macro to move the data in each row to one column:

text to column by delimiter results:

columnA columnB columnC columnD
A1, B1,C1,D1
A2,B2,
A3
A4,B4,C4
...

I want to eventually have:
A1
B1
C1
D1
A2
B2
A3
A4
B4
C4

So I wrote a macro like the below, but somehow it did not work. Also i
includes all Ax, Bx, Cx, Dx to the new column, not the ones that ha
values in it. Can you help me to modify the code to make it work
Thanks.

Sub Macro1()

Dim j As Integer
Dim i As Integer

For i = 1 To 6078

j = 1

Range("B" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1


Range("C" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("D" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("E" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("F" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("G" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("H" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

Range("I" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & j).Select
ActiveSheet.Paste

j = j + 1

i = i + 1

Next

End Su