Mail Merge: How to retain numeric formatting between Excel and Word 2007

To retain numeric formatting applied in Excel (e.g., zip codes, currency, dates, phone numbers, etc.) when you use that data during a mail merge in Word 2007, you must set two options within Word:

NOTE
Numbers stored as "Text" are not effected by the mail merge.

How to set the file format conversion option

NOTE
This step only has to be done once.

  1. Open MS Word 2007.
  2. Click the Microsoft Office Button in the upper-left corner of the window.
  3. Click Word Options.
  4. Click Advanced.
  5. Scroll down until you locate the General section.
  6. Click in the checkbox in front of Confirm file format conversion on open to insert a check mark (if necessary). The Word Options dialog box should look similar to the following:

    Confirm at Conversion window

  7. Click OK to close the dialog box.
  8. Quit MS Word.
  9. Reopen MS Word to ensure the change has taken effect.

How to set a Dynamic Data Exchange (DDE) path between Word and Excel

NOTE
This step must be done every time you use the mail merge feature with formatted numerical Excel data.

  1. Open MS Word 2007.
  2. Click the Mailings tab.
  3. Click Start Mail Merge. (If you prefer to use the Mail Merge Wizard, the option is at the bottom of this menu.)
  4. Click Select Recipients.
  5. Click Use Existing List.
  6. Choose the Excel file you want to use.
  7. Click Open. You will see the Confirm Data Source window, which should look similar to the following:

    DDE Setting

  8. Click the checkbox in front of Show all to insert a check mark (if necessary).
  9. Click MS Excel Worksheets via DDE (*.xls).
  10. Click OK.
  11. Choose your data: entire spreadsheet or identified print areas if available.
  12. Click OK.
  13. Set up your mail merge by using the Insert Merge Field menu.
  14. Click Preview Results to confirm that numeric formatting is carried over from your spreadsheet.

Last updated January 27, 2009