How to Fix the Formatting of an Excel Mail Merge Field in a Word Document

Microsoft Excel Tips & Tricks, Microsoft Word Tips & Tricks
formatting problems with Excel mail merge field in a Word document, Excel data doesn't keep formatting in Word mail merge

The mail merge feature in Microsoft Word is a time-saving option to create repetitive documents from letters to contracts to emails to labels and more. However, you may notice a glitch that happens when you create a mail merge in Microsoft Word with Excel data, that is, the number formatting in Excel doesn’t show up correctly in the Word document. For example, a dollar amount of $1,234.00 in an Excel worksheet used as a data source may display as 1234 in a Word document. You might also see formatting issues with other numbers that are displayed as text such as zip codes, lease or property numbers, and other identifiers.

When you see formatting problems, how can you format the Excel mail merge field to accurately display the formatted number in a Word mail merge main document? The solution to controlling the number and currency formatting of Excel data in Word is to add a numeric switch (previously called a picture switch) to the mail merge code or MERGEFIELD. Try this formatting fix on letters, emails, and other documents when Excel data does not retain its formatting in mail merge operations in Word.

formatting Excel mail merge field in a Word document

For example, in this sample letter, the Amount field is a merge field from a linked recipient list created in Excel. When the results are previewed, the amount displays as 1000 without the currency formatting in the original data source. With a simple edit, the field can be changed to display the formatting you want in the document.

Formatting an Excel Mail Merge Field

To apply number formatting to an Excel mail merge field:

formatting Excel mail merge field in a Word document
  1. Select the field, such as Amount, as shown in this example.
  2. Press [Alt] + F9 to reveal the field coding. Or, right-click on the mail merge field and choose the Toggle Field Code option. You should now see the actual field code for the field which should look something like { MERGEFIELD Amount }.
  3. Edit the field by simply inserting a numeric switch code to the end of the field. For instance, to show a currency format for this example, change the field to show as { MERGEFIELD Amount \# $,0.00 } (see other examples below).
  4. Press [Alt] + F9 again.
  5. Position the cursor anywhere in this field and press the [F9] function key to update it. Or, right-click the field and choose Update Field.
  6. Preview the results to confirm the number displays with the formatting you want to see in the document. For this example, the amount will display as $1,000.00.
  7. Run the mail merge.

Options for the Excel Mail Merge Field Numeric Picture Switch

The \# $,0.00 in the field is a type of numeric picture switch. Other options for formatting Excel numeric fields in a mail merge include:

Excel DataField CodeWord ResultDisplayed Result
1000{ MERGEFIELD Amount \# ,0 }Rounded whole numbers, thousands separator1,000
1000.25{ MERGEFIELD Amount \# $,0 } Rounded whole numbers, currency, thousands separator $1,000
1234.5{ MERGEFIELD Amount \# ,0.00 } 2 decimal places, thousands separator 1,234.50
1234.5{ MERGEFIELD Amount \# $,0.00 } 2 decimal places, currency, thousands separator *$1,234.50
1234.5{ MERGEFIELD Amount \# $#,##0.00 } Same as above * $1,234.50
-1234.5{ MERGEFIELD Amount \# “$,0.00;($,0.00);’-‘” } Currency, with brackets around negative numbers and a hyphen for 0 values ($1,234.50)
5{ MERGEFIELD Amount \# 0.00 } 2 decimal places5.00
0.45{ MERGEFIELD Amount \# 0.## } 2 decimal places, leading zero0.45
0.4{ MERGEFIELD Amount \# #.## } 2 decimal places only if values.4
1234 { MERGEFIELD ZipCode \# 00000 } 5 numeric positions, zip code or other number01234

For these formatting options, the precision of the displayed value for the Excel mail merge field is controlled by the 0.00. You can use anything from 0 to 0.000000000000000.
* Including 0 in a numeric switch code creates a required or fixed position whereas the # sets a pattern without a required 0 or placeholder.

Try these fixes for formatting Excel mail merge fields to get the results you want in your documents. And, for more options, find out how to customize the formatting of date and text mail merge fields.

Plus, discover more ways to save time editing and formatting your Word documents at TheSoftwarePro.com/Word.

By Dawn Bjork, MCT, MOSM, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Software Consultant

Previous Post
SmartArt Graphics: One Picture is Worth More Than a Thousand Words in Microsoft Office
Next Post
How to Change the Formatting of a Date or Text Mail Merge Field in a Word Document

Related Posts

Categories

  • Your cart is empty.
Menu