How can I spread a column with 5,000 numbers into multiple column.

C

c_michigan

I have imported data in one column that is 5,000 cells long. How can I make
this into mulitple columns so it is easier to view/print?
 
G

Gord Dibben

Manually............

If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 10 columns and down 500
rows will produce your 10 columns of 500 rows. Any more than 400 original
rows, you do the math and make alterations.

=INDIRECT("A"&(ROW()+(COLUMN()-2)*500))

The 2 refers to the column of Cell B1; if you're putting the formula in
a different column, use the appropriate number for that column.

Copy>Paste Special(in place) the results then delete the original column A.

VBA Macro to snake the columns top to bottom...1 to 50 down then 51 to 100
down

Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub

Gord Dibben Excel MVP
 
D

Dave Peterson

Maybe just open the text file in MSWord and use Format|columns.

(Or even copy the range to MSWord and use that same technique.)
 
Top