changing colums to rows for a mailer

**exodus125** · 05-15-2009, 04:34 PM

have a co-worker that came to me for some help. She has a mailing list in the following format:

John Doe
1234 SW 23rd Rd
Miami, FL 12345

Jane Smith
12345 SW 12 ST
Apt 23
Miami, FL 12343

Jane White
receptionist
1321 SW 11 ST
Apt 12
Miami, FL 33121

and it needs to be moved to look like this:
NAME ADDRESS CITY STATE ZIP
John Doe 1234 SW 12 St Apt 3 Miami FL 33143
Joe Black 3241 NW 34 St Miami FL 31223
Maggie White 5434 NE 34 St Miami FL 32343

she needs the info to generate an auto mailer, where the name goes on a certain spot, then the address, and so on. The problem is that sometimes there's irrelevant info (like receptionist on Jane White). That info needs to be cut out from the mailer. Also, because some of the people have no apt numbers, the info may not line up. Under the address line the apt # must be on there, and city state and zip have to be separated.

If anyone has any ideas that can help it would be great. I already told her she could use past special and transpose so that she doesnt have to enter everything manually (how shes been doing for the past 3 days) But theres thousands of enteries to be done. I am also not sure if theres a way to generate a mailer from excel to word (she is using Excel info and generating the letters in Word) that can use the present format she is changing, if anyone knows about anything like that, also let me know. Thanks in advance!

**leto2** · 05-15-2009, 07:14 PM

The only way that I can think to do this is with a macro with some built-in logic to identify the different pieces. For example, it would look for a comma in the row to identify city; it would look for a number in the first character of the row to identify an address; etc. This would probably take some time and would still not be perfect.