Here is a macro that will take all of the data in a selected range of cells and copy it to a column.
To install the macro...
Alt+F11 to open the VBA editor
Select from the VBA menu, Inset\Module
Paste the code below in the edit window.
Back in Excel, select the range of cells you want to copy to one column.
Run the macro Data_to_Column
The macro will prompt you to select a starting cell to copy the data to.
---
Sub Data_to_Column()
Dim rData As Range
Dim r As Range, c As Range
Dim rStart As Range
Dim counter As Integer
Set rData = Selection
On Error Resume Next
Application.DisplayAlerts = False
Set rStart = Application.InputBox( _
Prompt:="Select the 1st cell you want to copy the data to.", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If rStart Is Nothing Then Exit Sub
For Each c In rData.Columns
For Each r In rData.Rows
If Not IsEmpty(Cells(r.Row, c.Column)) Then
rStart.Offset(counter, 0) = Cells(r.Row, c.Column)
counter = counter + 1
End If
Next r: Next c
End Sub