I want to convert a spreadsheet that has 9 fields into a block of text.

From this in Line 1 in an excel file (9 fields):
300 Shady Grove 15720 Shady Grove
Rd. Gaithersburg MD 20877 Patrick Cowen 301-948-1390

to this format in Word:
AMERICAN FOLK ART MUSEUM
45 W. 53rd Street, New York, NY 10019
Website: www.folkartmuseum.org
Contact: Katie Hush
Contact Title: Associate Director of Special Events
Phone: 212-977-7170 ext. 308
Email:

Is there a formula?

Please help. The spreadsheet is in Excel 97-2003 but I have Office 2007.

Thanks,
Lee

This kind of question probably belongs in the Excel Programming area, not the
Excel Functions area, but I'll try to offer a solution for you here.

With your data on Sheet1, run this macro:

Sub makelist()
LastAccount = Range("A1", Range("A1").End(xlToRight)).count
RowCount = 1
For j = 1 To LastAccount
ThisAccount = Cells(1, j)
MyRow = 2
Do
If Cells(MyRow, j) = "" Then Exit Do
Worksheets("Sheet1").Cells(RowCount, 1) = ThisAccount
Worksheets("Sheet2").Cells(RowCount, 2) = Cells(MyRow, j)
MyRow = MyRow + 1
RowCount = RowCount + 1
Loop
Next j
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"LeeE" wrote:

I want to convert a spreadsheet that has 9 fields into a block of text.

From this in Line 1 in an excel file (9 fields):
300 Shady Grove 15720 Shady Grove
Rd. Gaithersburg MD 20877 Patrick Cowen 301-948-1390

to this format in Word:
AMERICAN FOLK ART MUSEUM
45 W. 53rd Street, New York, NY 10019
Website: www.folkartmuseum.org
Contact: Katie Hush
Contact Title: Associate Director of Special Events
Phone: 212-977-7170 ext. 308
Email:

Is there a formula?

Please help. The spreadsheet is in Excel 97-2003 but I have Office 2007.

Thanks,
Lee