r_i_x
asked on
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?
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?
ASKER
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.
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
Joanne
ASKER
This is the file i've used unsuccessfully.
test.xls
test.xls
It still works perfectly for me.... can you send me a sample primary file that isn't working?
Merged.jpg
Merged.jpg
ASKER
This doesn't accept xlsx files. Send via e-mail?
ASKER
Here's my results
ugly-merged.jpg
ugly-merged.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Glad to be of assistance! Thanks very much.
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