Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert multiple columns to a single column? scottflinders Excel Discussion (Misc queries) 5 November 12th 06 03:13 PM
to convert columns to rows having mulit independent group columns Quacy Excel Worksheet Functions 1 August 22nd 06 11:20 PM
convert two columns into one column? sherry Excel Discussion (Misc queries) 1 February 8th 06 04:33 PM
How to convert columns of data to one column of text devlkat Excel Worksheet Functions 3 April 6th 05 04:08 PM
how to convert multiple columns of data into one single column? Ah 3 Excel Worksheet Functions 1 November 12th 04 03:33 PM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"