Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Excel data does not retain its formatting in mail merge operations in Word

Excel data does not retain its formatting in mail merge operations in Word

View products that this article applies to.

Symptoms

If you perform a mail merge in Microsoft Word and you use a Microsoft Excel worksheet as the data source for the recipient list, some of the numeric data may not retain its formatting when it is merged.

This behavior applies to formatted percentages, currency values, and postal codes, as shown in the following table: 

   Format          In Excel data     In Word MergeField
   ----------------------------------------------------
   Percentage          50%                  .5
   Currency         $12.50                12.5
   Postal Code       07865                7895

↑ Back to the top

Cause

This behavior occurs because the data in the recipient list in Word appears in the native format in which Excel stores it, without the formatting that is applied to the worksheet cells that hold the data.

↑ Back to the top

Resolution

To resolve this behavior, use one of the following methods.

Method 1

Use Dynamic Data Exchange (DDE) to connect to the Excel worksheet that contains the data that you want to use. To do this, follow these steps.

Word 2002 and Word 2003

  1. Start Word, and then open a new blank document.
  2. On the Tools menu, click Options, and then click the General tab.
  3. Click to select the Confirm conversion at Open check box, and then click OK.
  4. On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.
  5. In the Mail Merge task pane, select the type of document that you want to work on, and then click Next.
  6. Under Select starting document, select the starting document that you want to use, and then click Next.
  7. Under Select recipients, click Use an existing list, and then click Browse.
  8. In the Select Data Source dialog box, locate the folder that contains the Excel workbook that you want to use as your data source, select the workbook, and then click Open.
  9. In the Confirm Data Source dialog box, click MS Excel Worksheets via DDE (*.xls), and then click OK.
  10. In the Microsoft Excel dialog box, under Named or cell range, select the cell range or worksheet that contains the data that you want to use, and then click OK.
    NOTE: Your data now appears in the Mail Merge Recipients dialog box with the same formatting that appears in the Excel worksheet.

Word 2007 and Word 2010

  1. Start Word, and then open a new blank document.
  2. Go to Word Options
    • In Word 2007, click the Office Button, and then click Word Options.
    • In Word 2010, click File, and then click Options.
  3. On the Advanced tab, go to the General section.
  4. Click to select the Confirm file format conversion on open check box, and then click OK.
  5. On the Mailings tab, click Start Mail Merge, and then select Step By Step Mail Merge Wizard.
  6. In the Mail Merge task pane, select the type of document that you want to work on, and then click Next.
  7. Under Select starting document, select the starting document that you want to use, and then click Next.
  8. Under Select recipients, click Use an existing list, and then click Browse.
  9. In the Select Data Source dialog box, locate the folder that contains the Excel workbook that you want to use as your data source, select the workbook, and then click Open.
  10. In the Confirm Data Source dialog box, click to select the Show all check box. Click MS Excel Worksheets via DDE (*.xls), and then click OK.
  11. In the Microsoft Excel dialog box, under Named or cell range, select the cell range or worksheet that contains the data that you want to use, and then click OK.
    NOTE: Your data now appears in the Mail Merge Recipients dialog box with the same formatting that appears in the Excel worksheet.

Method 2

Format the Excel field that contains the ZIP Code/Postal Code as text. To do this, follow these steps.

Word 2002 and Word 2003

  1. In Excel, select the column that contains the ZIP Code/Postal Code field.
  2. On the Format menu, click Cells.
  3. Click the Number tab.
  4. Under Category, click Text, and then click OK.
  5. Save the data source. Then, continue with the mail merge operation in Word.

Word 2007 and Word 2010

  1. In Excel, select the column that contains the ZIP Code/Postal Code field.
  2. On the Home tab, go to the Cells group. Then, click Format, and then click Format Cells.
  3. Click the Number tab.
  4. Under Category, click Text, and then click OK.
  5. Save the data source. Then, continue with the mail merge operation in Word.

↑ Back to the top

References

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

304387 Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word 2002 or Word 2003

↑ Back to the top

Keywords: kb, kbsweptsoltax, kbformat, kbbillprodsweep, kbprb, kbsoconvert, ocsentirenet

↑ Back to the top

Article Info
Article ID : 320473
Revision : 3
Created on : 4/17/2018
Published on : 4/17/2018
Exists online : False
Views : 530