Mail Merge with Carriage Return in Excel doesn't work in Word 2007

Have a spreadsheet with address data that has been used for many years to produce mailing labels in Word 2000/2003. The address field in the spreadsheet has the address in one field (column) and the City/State are on new lines using the ALT-ENTER command within the cell. A sample of the cell contents is:

123 Avenue Road
Toronto, ON
12345-001

Since upgrading to 2007, the Address line doesn't recognize the carriage return (or new line) and prints a special character. Also, I've tried text-to-column but cannot delimit on the carriage return (or new line).

Thoughts as to why/how to make this work?
LVL 1
r_i_xAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
r_i_x,
You will find it much better not to have the addresses in one block. It makes a mess of mailmerges when the addresses are in one block. Instead organise your data/address worksheet with a column for each possible line of an address. When you orgainise the mailmerge do not select the address block option, instead insert the fields one by one in the first label, format that one label as needed and then press the replicate button. Here are my full set of instructions for doing it in Excel 2002:
The stages are:

1. Prepare Excel database with column headings such as Title, Givenname, Familyname, Address1, Address2 and so on. Save and close the file.
2. In MSWord press the NewBlankDocument button. Select Tools/Letters and Mailings/MailMergeWizard/ and in the Taskpane select Labels. If the TaskPane is not visible go to View and tick Task Pane.
3. In the Task Pane press the Starting Document link
4. Press the LabelOptions link in the Task Pane
5. Select the label size that will allow you get all the data of one person onto one label - press OK
6. Press SelectRecipients/Browse and find the Excel data file. If you get it right you will see a dialogue box entitled Mail Merge Recipients - press OK
7. Press the link Arrange your labels in the Task Pane and in the next Pane select More Items
8. Making sure the cursor is in the first blank label, select and insert every field that you want in the label. Having done that press Cancel.
9. Re-arrange the fields in the FIRST label only, making sure that every field is still  surrounded with <<fieldx>>. Format that one label as required.
10. In the Task Pane under the heading Replicate Labels press the Update labels button (it doesn't look like a conventional button). All the labels will then be filled with the fields as laid out in the first label. Ignore the <<Next Record>> entries and DO NOT edit any of the labels.
11. In the Task Pane press the Preview Labels link
12. If the labels look OK then you can press the Complete the Merge link in the Task Pane and then press the Edit Individual Labels link to see the result. It's at that stage you can edit the labels if you want - as it's a separate document. Save it or print it.

You will find it easier if you have the MailMerge Toolbar visible for this whole task - View/Toolbars/MailMerge as you will then be able to do most stages witout using the Task Pane.

I know it's many stages but after a bit you will get used to it. You can of course save the set up and re-use it so that you only need to create the label merge once. I personally find that difficult so I just go through the routine each time - about 2 minutes - that's all.

Hope that helps

Patrick
0
r_i_xAuthor Commented:
Thanks Patrick. I'm well aware of how to use mail merge and also in the reasons behind keeping data in separate columns. Unfortunately, we have numerous spreadsheets throughout the org that are formatted that way as this DID work prior to Office 2007. I'm trying to prevent my involvement (IT Department) in changing all these spreadsheets and retraining.
0
Joanne M. OrzechManager, Document Services CenterCommented:
Is it possible to upload a portion or a redacted file?  Because I am unable to reproduce the problem using both Excel 2007 and Word 2007.....even though I've used Alt-Enter in the Excel spreadsheet... it still merges properly for me.

Joanne
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

r_i_xAuthor Commented:
This is the file i've used unsuccessfully.
test.xls
0
Joanne M. OrzechManager, Document Services CenterCommented:
It still works perfectly for me.... can you send me a sample primary file that isn't working?
Merged.jpg
0
r_i_xAuthor Commented:
This doesn't accept xlsx files. Send via e-mail?
0
r_i_xAuthor Commented:
Here's my results
ugly-merged.jpg
0
Joanne M. OrzechManager, Document Services CenterCommented:
First of all, I think you need to modify your Normal style to be single spaced, and have zero space after....

Yes - send me the Excel file via email


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r_i_xAuthor Commented:
Moral of the story: don't use Address Block but instead format the fields yourself. I really should have tried that myself but don't see how/why this would be different as it just matches the fields automatically. Thanks to JOrzech for the help and pointing me to the solution.
0
Joanne M. OrzechManager, Document Services CenterCommented:
Glad to be of assistance!  Thanks very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.