Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert multiple columns to a single column? | Excel Discussion (Misc queries) | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
convert two columns into one column? | Excel Discussion (Misc queries) | |||
How to convert columns of data to one column of text | Excel Worksheet Functions | |||
how to convert multiple columns of data into one single column? | Excel Worksheet Functions |