Mailing List Problem

A

AdGuy

I received a mailing list that someone obviously just "cut and pasted
and poses several challenges. The file contains over 2,000 contac
records and appears like this:

In A1:
In B1: Teresa L Perry
In C1: 594 Postwoods Dr
In D1: REYNOLDSBURG, OH 43068
In E1:
In F1:
In G1: Valarie Aaron
In H1: 1654 Smith Rd.
In I1: OBETZ, OH 43207
In J1:
In K1:
In L1: Mr Mark Abbott
In M1: 4626 Channing Terrace Apt D
In N1: COLUMBUS, OH 43232

...and so on down the line. How do I adjust the spreadsheet so th
records appear as follows (please read through all three examples s
you can understand the different challenges):

In A1: Teresa
In A2: L
In A3: Perry
In A4: 594 Postwoods Dr
In A5: Reynoldsburg (note Proper case and no comma)
In A6: OH
In A7: 43207
In B1: Valarie
In B2: (note no mid initial, so left blank)
In B3: Aaron
In B4: 1654 Smith Rd.
In B5: Obetz
In B6: OH
In B7: 43207
In C1:In A1: Mark (notice we removed the prefix of "Mr")
In C2: (again no mid initial, so left blank)
In C3: Abbott
In C4: 4626 Channing Terrace Apt D
In C5: Columbus
In C6: OH
In C7: 43232

I'm stumped on this one but it's probably a piece of cake for yo
folks. Any help would be GREATLY appreciated!!

AdGuy :confused
 
D

David McRitchie

On another sheet you can use the formula

=OFFSET(contacts!$A$1,ROW()*5-6+COLUMN(),0)

so that you don't get 0 for the spaces you could append a null string
=OFFSET(contacts!$A$1,ROW()*5-6+COLUMN(),0)&""

Copy the formula to the right and the the row down as needed with the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

You might want to separate out the zip state and zip code with
data, text to columns with comma and space as delimiters with the
treat consecutive delimiters as one.

Then convert the Towns to proper case with
http://www.mvps.org/dmcritchie/excel/proper.htm

Excel does not provide decent facilities for labels, so you probably want to use
MS Word, MailMerge for that see
http://www.mvps.org/dmcritchie/excel/mailmerg.htm



---

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Top