A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

convert one column into many columns



 
 
Thread Tools Display Modes
  #1  
Old May 14th 07, 01:59 PM posted to microsoft.public.excel.worksheet.functions
ajay
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
Ads
  #2  
Old May 14th 07, 02:02 PM posted to microsoft.public.excel.worksheet.functions
JE McGimpsey
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  
Old May 14th 07, 02:12 PM posted to microsoft.public.excel.worksheet.functions
ajay
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  
Old May 14th 07, 02:48 PM posted to microsoft.public.excel.worksheet.functions
Sebation.G[_2_]
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  
Old May 14th 07, 03:03 PM posted to microsoft.public.excel.worksheet.functions
Sebation.G[_2_]
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  
Old May 14th 07, 03:07 PM posted to microsoft.public.excel.worksheet.functions
bj
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  
Old May 14th 07, 03:59 PM posted to microsoft.public.excel.worksheet.functions
ajay
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  
Old May 14th 07, 09:31 PM posted to microsoft.public.excel.worksheet.functions
bj
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02: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 03: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 02:33 PM


All times are GMT +1. The time now is 01:20 PM.


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