Convert long column to rows

[Excelchallenged]

I have exported a large database that is all in one column, how do I change
it to rows? The Copy, Paste Special, Transpose doesn't work because of the
length of the column.

Thank you!

 

[Eduardo]

Hi,
I guess the information in that column is separate by , or spaces so you
need to do first a Text to columns , delimited, indicate the delimiters and
once you have the data in columns the transpose will work
Hope this help

 

[Gord Dibben]

You could transpose the long column to rows in pieces.

Say you have data in A1:A30000

You could split that into several chunks of rows.

Which version of Excel are you using and what is your actual range in column
A?


Gord Dibben MS Excel MVP

 

[Gary''s Student]

In this example, all the data is in column A in Sheet1. The following macro
will copy it into rows on Sheet2:

Sub xformit()
Dim s1 As Worksheet, s2 As Worksheet
Dim i As Long, j As Long, k As Long
Dim n As Long
Set s2 = Sheets("Sheet2")
Set s1 = Sheets("Sheet1")
s1.Activate
j = 1
k = 1
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
s2.Cells(j, k).Value = Cells(i, 1).Value
k = k + 1
If k > Columns.Count Then
k = 1
j = j + 1
End If
Next
End Sub