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!

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

"Excelchallenged" wrote:

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!

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

On Tue, 17 Mar 2009 08:18:09 -0700, Excelchallenged
wrote:

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!

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
--
Gary''s Student - gsnu200839


"Excelchallenged" wrote:

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!