move list of numbers from one column to multiple columns

[coach eo]

How do I move a list of 15000 numbers from one column to 6 or 8 to make it
easier to display.

[dlw]

cut and paste?

"coach eo" wrote:

> How do I move a list of 15000 numbers from one column to 6 or 8 to make it
> easier to display.

[mphell0]

What do your numbers look like now and how do you want them to look?

[coach eo]

15000 numbers, I was looking for an automated process

"dlw" wrote:

> cut and paste?
>
> "coach eo" wrote:
>
> > How do I move a list of 15000 numbers from one column to 6 or 8 to make it
> > easier to display.

[mphell0]

You're going to have to give more details. Can you give an example of what
you are trying to do? What part of the number would go into another column?

[coach eo]

15000 telephone numbers. I want them to be in 6 or 8 columns, instead of 1

"mphell0" wrote:

>
> What do your numbers look like now and how do you want them to look?
>
>
> --
> mphell0
> ------------------------------------------------------------------------
> mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> View this thread: http://www.excelforum.com/showthread...hreadid=513237
>
>

[mphell0]

If you have a number like 1-123-456-7890 and you want it to be

1 in a column
123 in another
456 in another
7890 in another

You can use Data->Text to columns selected delimited and for delimiters
select other and enter "-"

[coach eo]

I have imported a file of telephone numbers. There are 15,000 numbers in a
single column. If I printed or displayed the numbers, I would only see 60
(or so)per page. I would like to move spread those 15,000 telephone numbers
in multiples columns. So when I run a report, I have numbers in 6 or 8
columns rather than one. Purely for printing and display.

"mphell0" wrote:

>
> You're going to have to give more details. Can you give an example of
> what
> you are trying to do? What part of the number would go into another
> column?
>
>
> --
> mphell0
> ------------------------------------------------------------------------
> mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> View this thread: http://www.excelforum.com/showthread...hreadid=513237
>
>

[vezerid]

Do the following:

Decide on the number of columns that you want. Let us say you want 6
columns. In the row put the numbers 0...5 (a total of 6 numbers)

Assuming you want your transferred data to start from row 2, below the
0, use the following formula:

=OFFSET($A$2,(ROW()-2)*6+D$1,0)

In this formula, we assume your phone numbers start from A2, that you
have 6 columns and that the 0...5 start from D1. Copy as far down and
across as necessary.

HTH
Kostis Vezerides

[Kevin Vaughn]

You could do something like this. Since 15000/6 = 2500, you could hit Cntl-g
(for goto, type the range a2500:a5000 (or something very similar) hit cntl-x
(for cut,)
then right arrow end up (hopefully you will now be in b1,) and cntl-v for
paste. Repeat the process going from, for instance a5001:a7500 (or
thereabouts.) When finished doing all 6 (or 8) columns, go down to row 2500.
Hold shift and then hit cntl-end to go to the bottom of your spreadsheet,
right click and hit delete and answer entire row. Then save (this should
make it so Excel doesn't think your used range is as far down as it was
originally.)
--
Kevin Vaughn


"coach eo" wrote:

> 15000 telephone numbers. I want them to be in 6 or 8 columns, instead of 1
>
> "mphell0" wrote:
>
> >
> > What do your numbers look like now and how do you want them to look?
> >
> >
> > --
> > mphell0
> > ------------------------------------------------------------------------
> > mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> > View this thread: http://www.excelforum.com/showthread...hreadid=513237
> >
> >

[coach eo]

Thank you. I will try that.

"vezerid" wrote:

> Do the following:
>
> Decide on the number of columns that you want. Let us say you want 6
> columns. In the row put the numbers 0...5 (a total of 6 numbers)
>
> Assuming you want your transferred data to start from row 2, below the
> 0, use the following formula:
>
> =OFFSET($A$2,(ROW()-2)*6+D$1,0)
>
> In this formula, we assume your phone numbers start from A2, that you
> have 6 columns and that the 0...5 start from D1. Copy as far down and
> across as necessary.
>
> HTH
> Kostis Vezerides
>
>

[coach eo]

Thanks. Someone else gave me an automated way. See vezerid

"Kevin Vaughn" wrote:

> You could do something like this. Since 15000/6 = 2500, you could hit Cntl-g
> (for goto, type the range a2500:a5000 (or something very similar) hit cntl-x
> (for cut,)
> then right arrow end up (hopefully you will now be in b1,) and cntl-v for
> paste. Repeat the process going from, for instance a5001:a7500 (or
> thereabouts.) When finished doing all 6 (or 8) columns, go down to row 2500.
> Hold shift and then hit cntl-end to go to the bottom of your spreadsheet,
> right click and hit delete and answer entire row. Then save (this should
> make it so Excel doesn't think your used range is as far down as it was
> originally.)
> --
> Kevin Vaughn
>
>
> "coach eo" wrote:
>
> > 15000 telephone numbers. I want them to be in 6 or 8 columns, instead of 1
> >
> > "mphell0" wrote:
> >
> > >
> > > What do your numbers look like now and how do you want them to look?
> > >
> > >
> > > --
> > > mphell0
> > > ------------------------------------------------------------------------
> > > mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> > > View this thread: http://www.excelforum.com/showthread...hreadid=513237
> > >
> > >

[coach eo]

thank you. Someone gave me another. See vezerid I will try them all.

"mphell0" wrote:

>
> If you have a number like 1-123-456-7890 and you want it to be
>
> 1 in a column
> 123 in another
> 456 in another
> 7890 in another
>
> You can use Data->Text to columns selected delimited and for
> delimiters
> select other and enter "-"
>
>
> --
> mphell0
> ------------------------------------------------------------------------
> mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> View this thread: http://www.excelforum.com/showthread...hreadid=513237
>
>