convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
convert one column into many columns
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 |
All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com