This tutorial tackles common formatting problems when mail merging from Excel to Word, focusing on numbers, dates, percentages, and currency. Previous articles covered the basics of mail merge; however, maintaining correct formatting often presents challenges. Numbers, especially those with leading zeros (like zip codes), percentages, and currency values, frequently lose their formatting during the merge process.

The root cause is Word's default use of the OLE DB connection, which imports data but not formatting. This results in Word displaying the underlying numerical value rather than the formatted cell value from Excel.

Illustrative Example:

An Excel sheet might show:

But the Word mail merge document might display:

Notice the loss of leading zeros in zip codes, the absence of currency symbols and formatting in monetary values, and the inconsistent display of percentages and dates.

Solution: Dynamic Data Exchange (DDE)

The most efficient solution is to use DDE to connect to your Excel workbook. Before starting the mail merge:

1. Navigate to File > Options > Advanced.
2. In the General section, check "Confirm file format conversion on open" and click OK.
3. Initiate the mail merge, selecting "Use an existing list" for recipients.
4. Browse to your Excel file, selecting it and clicking Open.
5. In the Confirm Data Source dialog, check "Show all," choose *MS Excel Worksheets via DDE (.xls)*, and click OK*.
6. Select Entire Spreadsheet and click OK.

While DDE connection might take time, it ensures all formats are preserved. After connecting, uncheck "Confirm file format conversion on open" to avoid repeated prompts. The result:

Alternative: Numeric Switches (Field Codes)

If DDE isn't feasible, you can control formatting individually using numeric switches within Word's merge fields. This involves manipulating field codes:

1. Select the merge field.
2. Press Shift F9 to reveal the field code (e.g., { MERGEFIELD Number }).
3. Append a numeric switch code.
4. Press F9 to update.
5. Press Shift F9 to hide the code.

Examples:

• Number Formatting:

  • \\# 0: Rounded whole number
  • \\# ,0: Whole number with thousands separator
  • \\# ,0.00: Number with two decimals and thousands separator

• Currency Formatting:

  • \\# $,0: Rounded dollar with thousands separator
  • \\# $,0.00: Dollar with two decimals and thousands separator
  • \\# "$#,##0.00;($#,##0.00);'-'" : Dollar with parentheses for negative numbers, hyphen for zero.

• Percentage Formatting: If Excel uses General/Number format: \\# 0.00% or \\# 0%. If Excel uses Percent format, use a formula: {=?Percent?\*100 \\# 0%} or {=?Percent?\*100 \\# 0.00%}.

• Date/Time Formatting: Use codes like \\@ "M/d/yyyy", \\@ "d-MMM-yy", \\@ "h:mm AM/PM", etc.

• Current Date/Time: Use Alt Shift D (DATE) and Alt Shift T (TIME), then add numeric switches for custom formats.

Preserving Leading Zeros:

Format zip code columns in Excel as Text before the merge. However, if using Special or Custom formats in Excel, DDE is recommended to avoid manual zero re-entry.

By employing these methods, you can effectively manage formatting during mail merges, ensuring accurate and professional-looking documents.

The above is the detailed content of How to fix Mail Merge formatting issues in Word. For more information, please follow other related articles on the PHP Chinese website!