The mail merge feature in Microsoft Word is a time-saving option for creating 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.
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:
- Select the field, such as Amount, as shown in this example.
- 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 }.
- 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).
- Press [Alt] + F9 again.
- 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.
- Preview the results to confirm the number displayed with the formatting you want to see in the document. For this example, the amount will display as $1,000.00.
- 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 Data | Field Code | Word Result | Displayed Result |
| 1000 | { MERGEFIELD Amount \# ,0 } | Rounded whole numbers, thousands separator | 1,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 places | 5.00 |
| 0.45 | { MERGEFIELD Amount \# 0.## } | 2 decimal places, leading zero | 0.45 |
| 0.4 | { MERGEFIELD Amount \# #.## } | 2 decimal places only if values | .4 |
| 1234 | { MERGEFIELD ZipCode \# 00000 } | 5 numeric positions, zip code or another number | 01234 |
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.
© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
