Condensing Imported fixed-width text with multiple data lines to one Excel row

[kwstroud]

I have a text file report with about 1,000 names listed as below. The
address is always one row down from name, the City-State-Zip is always
2 down from name, the DOB is 5 down. The email is always one column
over and 5 rows down.

Joe A Blow
555 LAREWOOD DRIVE
Vass, NC 28215


DOB = 1960-07-27 email: (e-mail address removed)

My ideal end-product is to have each customer on one row with the
following column headings: Name, Address, City-State-Zip, DOB, and
email. Any ideas on how to get started?

 

[Mikeopolo]

Try this (credit to Biff for his method of condensing):

Import the data to column A.

In B1 type: =INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)
This formula effectively looks up each cell in column A in turn and
places them from left to right and down the rows.
Copy this to columns C:G, and hide cols E-F (will be zero) (don't
delete the cols)

In H1 type: =MID(G1,1,FIND("email:",G1)-1)
in I1 type: =MID(G1,FIND("email:",G1)+7,99)

This will separate the last cell into 2 values using the key phrase
"email:"

Copy the formula in cols B:I as far down as need be.

Et voila! (I hope)...

Regards
Mike