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 crash that occurs when you create a mail merge in Microsoft Word with Excel data. i.e. the number format in excel is not displayed correctly in the word document. For example, a dollar amount of $1,234.00 in an excel spreadsheet used as a data source may display as 1234 in a word document. You may also notice formatting issues with other numbers displayed as text, such as zip codes, property or lease numbers, and other identifiers.
When you see formatting issues, 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 control the number and currency formatting of excel data in word is to add a number change (previously called image change) to the mail merge code or merge field. Try this formatting fix on letters, emails, and other documents when excel data doesn’t retain its formatting in word mail merge operations.
for example, in this sample letter, the amount field is a merged field from a linked recipient list created in excel. when previewing the results, the amount is displayed as 1000 without currency formatting in the original data source. With a simple edit, the field can be changed to display the format you want in the document.
format 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 encoding field. Or, right-click 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 simply by inserting a numeric change code at the end of the field. for example, to display a currency format for this example, change the field to display as { combined field amount # $,0.00 } (see other examples below).
- press [alt ] + f9 again.
- position the cursor anywhere in this field and press the function key [f9] to update it. or right-click the field and choose Update Field.
- Preview the results to confirm the number is displayed in the format you want to see in the document. for this example, the amount will display as $1,000.00.
- run the mail merge.
options for excel mail merge field numeric makeover
# $,0.00 in the field is a numeric makeover type. Other options for formatting excel numeric fields in a mail merge include:
For these format options, the precision of the displayed value for the excel mail merge field is controlled by 0.00. you can use anything from 0 to 0.000000000000000. * including 0 in a numeric switch code creates a mandatory or fixed position, while # establishes a pattern without a mandatory 0 or placeholder.
Try these fixes to format excel mail merge fields to get the results you want in your documents. And for more options, learn how to customize the format of date and text mail merge fields.
Also, discover more ways to save time editing and formatting your word documents at thesoftwarepro.com/word.
© Alba Bjork, MCT, MOSM, CSP®, The Software Pro®Microsoft Certified Trainer, Productivity Speaker, Certified Professional Speaker