convert one column into many columns

[Guest]

Hi,
I have a column which contains data that shd go into 9columns.

can anybody tell me how to convert this single column into 9 columns? I have
a delimiter at every 9th record.

thanks,
Ajay

 

[JE McGimpsey]

One way:

Choose Data/Text to Columns/Delimited

 

[Guest]

Hi,
The delimiter is at every 9th record..so Excel is converting the 9th, 18th,
27th...cell into next column.

Any more ideas?
thanks.

 

[Sebation.G]

hi
ALT+F11 -> copy this code
Sub test()
Dim St() As String
Dim A As Long
Dim B As Long
On Error Resume Next
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For A = 1 To lastrow
St = Split(Cells(A, 1), ";")
For B = 0 To UBound(St)
Cells(A, 2 + B) = St(B)
Next
Next
End Sub
then hit F5(run)

Hope this can be helpful

 

[Sebation.G]

P.S:
St = Split(Cells(A, 1), ";")
u can change ";" with the delimiter in your sheet

 

[Guest]

If I am reading your question right you want to move A1:A9 to A1:I9
and A10:A18 to A2:I2
etc.
or whatever columns you want to use

if this is correct
for data in column A
in B1 enter
=INDIRECT("A"&((ROW()-1)*7+COLUMN()-1))
copy over to column H and as far down as needed
select B:H copy and paste special values
delete column A

you will have to adjust the row and column adders for other than A1 as your
first data point.
be sure you try this on a copy before you put it in the final document.

 

[Guest]

Dear Sebation,
When I tried your script, it could change only the record with the
delimiter, i.e every 9th record.

Dear bj,
This trick worked...I changed the formula to *9 instead of 7 and it worked
like charm.
Thank you very much both of you!

best regards,
Ajay

 

[Guest]

sorry about that, when I was testing it, I had to change to *9 I must have
copied the wrong cell to paste into the suggestion