Converting "Rolodex" text format for importing

[Junto]

I need to transfer a txt listing of customer information from a Rolodex-type
format to a spreadsheet. It is a very old system and the only way I could
get the data I needed was via this Rolodex format. However, the listing
does have a reproducible format. I’d like to move the individual rows into
columns and then slice and dice the individual columns to get the data into
additional columns for sorting, etc.

The Rolodex format I have is as follows:

Line 1 -- First Name, MI, Last Name (no commas)
Line 2 -- Address line #1
Line 3 -- Address line #2
Line 4 -- City, State, Zip code
Line 5 -- Chart #, DOB, Sex, Registration Date
Where: Chart# is 7 digits max
DOB is formatted MMDDYYYY
Sex is single alpha
Registration date is MMDDYY
Line 6 -- Home Telephone, Work Telephone
Where phone numbers are formatted
###_###_#### (3 spaces) ###_###_####
Line 7 -- Blank line
Line 8 -- Blank line

Then it starts over at Line 1… for approximately 800 people.
The thought of having to cut and paste all of this is killing me.
Any help would be appreciated.

Thanks.

 

[Sean Timmons]

So, in, say, cell B1, type A1.

In C1, type A2.

Go out to column G

Then, go to B2, equals A9

Same for C2 To G2.

Then, copy B1 through G2 and use fill to the botttom. Excel should have the
pattern figured out.

 

[Junto]

Sean (et al)...

The conversion didn't work exactly as noted, but by going a little deeper
with the copy, it worked like a charm. For example, rather than going to B2
and saying it was equal to A9, I went to B9 and said it was equal to A9. I
did as instructed and extended the row = column concept out to column G. I
copied two sets of addresses, including the last two blank rows of each, to
the end of the data.

One other thing I learned was that there weren't 800 addresses. There are
3185 addresses, and this work-around will allow me to spend some more time
with my family this weekend. I'm very grateful for the help.

Now, I need to go into the cell that has the City, State, Zip code and break
those into separate cells.

Many thanks.
Junto

Thanks.