Try this macro from Ken Johnson.

Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data........must have a blank row between sets
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim i As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
i = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
i = i + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(i, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Gord

On Wed, 15 Oct 2008 10:11:01 -0700, Ratatat
wrote:

Each line is in it's own cell and they are all in one column. Some are 3
lines, some are 4 and there is a blank space between them.

Example:

ABC Development, Inc.
9 ABC Road
Dallas, TX 75201

123 Realty LLC
10 Main St
PO Box 586
Pheonix, AZ 85001


"Gord Dibben" wrote:

Are the address blocks in one column?

Are the elements in a single cell or sets of cells?

Are the blocks consistent in size?

Can you post a sample of the current layout?

Not a file, just a copied sample.


Gord Dibben MS Excel MVP

On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

I have an excel file that contains 500+ address blocks because it was
originally a pdf file that I converted to excel format. My ultimate goal is
to do a mail merge from this file but that's not possible in the format it is
currently. Is there a way to pull the information from that sheet so that it
is organized into columns? Once it's there I can do text to columns to
separate items when necessary but I don't know how to get there.