Word 2010: More Mail Merge Tips – A Follow-Up

Some of you may recall our May 2012 blog post: Word 2010 Mail Merge: Changing the Date Format about changing the date format of a mail merge field in Microsoft Word. This sparked a number of positive responses from our readers and raised more questions about mail merge. I thought I’d address a couple of these questions today.

Question #1

“If I want the day of the week included in the date – how do I do that?”

-Holly

Answer

This is a great question with a very simple solution – all you have to do is adjust the date format to include the day of the week. The date format can be adjusted to whatever you’d like it to be.

In the example I gave, the format was “MMMM d, yyyy”; as you might expect, the date would end up looking like “January 3, 2013”. If I wanted that to include the day of the week to look like “Thursday, January 3, 2013”, I would simply adjust the format in the merge field to “dddd, MMMM, d, yyyy”. With the field coding visible (see original blog post for instructions on how to do this), your merge field should appear as follows:

«Date»

Your date format should change from this:

mail-merge-january-18-2013

To this:

mail-merge-friday-january-18-2013

Thanks for the question, Holly!

Question #2

David Rubin of BMO Nesbitt Burns Financial Services Inc. came up with another excellent question:

“What would be the entries for $000,000 and 0.00%? I can get the date to work, but am having trouble with the numeric [formatting].”

In other words, how do I set up a mail merge in Word to work with Excel data that has numeric formatting such as dollar amounts or percentages?

Answer

There are a few ways to accomplish this.

One way is to use the Dynamic Data Exchange (DDE). It is nowhere near as complicated as it may sound. After testing this out, I’ve found that this works best overall, particularly if you’re merging from a spreadsheet that already exists and contains a lot of numbers. I’ve outlined the steps for this approach below*:

*Please note:

  • These steps are for Word 2007/2010. If you are using an earlier version, please send us an email; we will be happy to send you the appropriate instructions for your version.
  • These steps are written under the assumption that you already know how mail merges works and how to perform one. If you need assistance with this, please let us know and someone can walk you through the process.

1. If it already exists, open your mail merge document. Otherwise, you can simply open a blank document.

2. Go into the Options dialogue box. If using 2007, this will be behind the Office button. In 2010, it’s under the File tab.

3. On the Advanced tab, scroll down to the General section (almost at the bottom):

mail-merge-screen

4. Check the box next to “Confirm file format conversion on open” and then click OK.

5. Begin your mail merge; you can do this through the Mail Merge wizard, but I prefer to skip that and go straight to Select Recipients. Whichever way you choose to get there, once you’ve selected your data source you will see the Confirm Data Source dialogue box.

6. Check the “Show all” box and then select “MS Excel Worksheets via DDE” and click OK:

mail-merge-confirm-data-source

7. In the Microsoft Excel dialogue box, under Named or cell range, select the data range or worksheet that contains that data you wish to use:

mail-merge-entire-spreadsheet

8. Now preview your results – all of your numeric values should appear as they were formatted in Excel!

mail-merge-end-screen

Another method is to work with the field codes directly in Word; this is what we did when we changed the date format. This really only works well when the Excel worksheet from which you’re merging only has one or two number columns. The third way, and probably least effective, is to format the Excel worksheet cells as text. This really only works well if you’re creating a new worksheet directly from the mail merge and if you’re not performing any calculations in the worksheet.

Thank you, David, for another great question!

We’d love to hear from you! If you have any tips or tricks related to any Microsoft Office program, please feel free to send us an email.

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

Advertisements

2 responses to “Word 2010: More Mail Merge Tips – A Follow-Up

  1. Hi All;
    I’ve been having trouble with a merge that draws info from Excel 2010 to Word 2010. It would display gibberish or truncate text in seemingly random merge fields depending on a number of factors, eg conditions selected for the merge on print or the sorting done in Excel itself. usually what was printed was a ‘date format’that made no sense. Occasionally I thought it was swearing at me.
    I followed all the good advice listed here & other places, did not help.
    Then I tried inserting a new row imediatly under the headings row in the Excel database & formating that to my requirements. I fixed the gibberish problem & the truncating issue at the same time.
    Gibberish by inserting the required final format, eg numbers as numbers, text as text & truncation was fixed by inserting the required nuber of characters into the newly inserted cell. (in my case aprox 2000 ‘a’ characters. Could have been ‘z’ or a space I’m guessing)
    Cheers, thanks for reading.

  2. SectorLearningSolutions

    Thank you, Mat! This is an excellent example of why it is always important to check the formatting in the originating spreadsheet before performing a mail merge. We appreciate the tip; if you have any others to share or if you have any other questions or comments, feel free to let us know!
    -Katie Caplan, Sector Learning Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s