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

One way:

Choose Data/Text to Columns/Delimited


In article ,
Ajay wrote:

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

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

Any more ideas?
thanks.

"JE McGimpsey" wrote:

One way:

Choose Data/Text to Columns/Delimited


In article ,
Ajay wrote:

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

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

--
Regards,

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

Any more ideas?
thanks.

"JE McGimpsey" wrote:

One way:

Choose Data/Text to Columns/Delimited


In article ,
Ajay wrote:

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

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

"Sebation.G" l...
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

--
Regards,

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

Any more ideas?
thanks.

"JE McGimpsey" wrote:

One way:

Choose Data/Text to Columns/Delimited


In article ,
Ajay wrote:

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

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.

"Ajay" wrote:

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

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


"bj" wrote:

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.

"Ajay" wrote:

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

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

"Ajay" wrote:

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


"bj" wrote:

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.

"Ajay" wrote:

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