Excel - convert row address listing to columnar database

[Thumbpick]

I have a mailing list of addresses in rows (mostly 3 rows), e.g.,
Mr. & Mrs. Anthony Strebek
8205 Dorset Lane
Springfield, MO 33345-1234

But some are 4 rows:
Mr. & Mrs. Albert Stratton
7789 Quonset Drive
Apt. 203
Portland, ME 22152

I want to convert it to a database, e.g.,
Streber,Anthony,8205,Dorset,Lane,Springfield,MO,33345

Is there a macro or formula to make this easier than retyping everything?

Thanks!

[Tom Ogilvy]

You want every space and "end-of-line" replaced with a comma?

--
Regards,
Tom Ogivy


"Thumbpick" <[email protected]> wrote in message
news:[email protected]...
> I have a mailing list of addresses in rows (mostly 3 rows), e.g.,
> Mr. & Mrs. Anthony Strebek
> 8205 Dorset Lane
> Springfield, MO 33345-1234
>
> But some are 4 rows:
> Mr. & Mrs. Albert Stratton
> 7789 Quonset Drive
> Apt. 203
> Portland, ME 22152
>
> I want to convert it to a database, e.g.,
> Streber,Anthony,8205,Dorset,Lane,Springfield,MO,33345
>
> Is there a macro or formula to make this easier than retyping everything?
>
> Thanks!

[elgeejay]

No…. sorry, poor example on my part. What I really need to do is convert a row format addr listing to a flat file, parsed addr listing (i.e., one row); e.g.,

From:
A1 John Doe
A2 Blast, Inc.
A3 123 maple Ave
A4 Philadelphia, PA 19026-2234

To:

A1 B1 C1 D1 E1 F1 G1 H1 I1
Doe John Blast Inc 123 Maple Ave Philadelphia PA 19024-2234

As I said, some are 3 line, some are 4. Also, I’ve inherited a poorly designed addr listing… sometimes the 2nd row is an addr, sometimes the company name(in which case the st addr is 3rd line). However, none is more that 4 rows. Each record is separated by a blank row.