Formatting Data from a Column to Rows in MS Excel

[egstatus]

Hi Averyone,
I get an excel file from a vendor and it has an Address Column that it looks as follows:

Address
50 MIDLAND AVENUE
HICKSVILLE, NY 11801-1510

DEPT 20-8025
PO BOX 5998
CAROL STREAM IL 60197

241 W 26TH ST
NEW YORK NY 10001

75 REMITTANCE DR #3011
CHICAGO IL 60675-3011

There is a blank line between each record.

I would like to format the data and put each part of the address into its own column so it looks as follows:

Addr1	    		Addr2			City		St	Zip
50 MIDLAND AVENUE				HICKSVILLE	NY	11801-1510
DEPT 20-8025		PO BOX 5998		CAROL STREAM	IL	60197
241 W 26TH ST					NEW YORK	NY	10001
75 REMITTANCE DR #3011				CHICAGO	        IL	60675-3011

The file has about 7000 records and I get it 2/week.
Can this be accomplished? If it is, can some give a hint where to star?

Thanks in advance.

Ed

 

[SkipVought]

Hi,

What code you have so far and where you're stuck.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[egstatus]

Hi Skip,

I don't have any code yet. I am actually learning the excel object to be able to manipulate rows and column.

I program in Ezc and Fpl. VBA is not my strongest programming language. I have googled about the excell object however there is quite a bit to learn before I can tackle this task.

My Idea is to create a For loop and start from the bottom up and each time I find a Cell Cut it and Paste it one row up and one Cell over. Something along those lines.

Do you have a piece of code that you can share?

Thanks

Ed.

 

[SkipVought]

How about some pseudo-code? Let's see your approach.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}