Mail Merge: Applying Currency Formatting to Numbers
Mail merge allows you to link a Word document to database of records. Sometimes the fields in that database contain numbers that need to be formatted in a certain way. For example, if one of your fields contain monetary values, these would need be formatted as currency.
Annoyingly when these values are appear in a mail merge document any formatting that you have applied in the database is lost. Here’s my database, created in Excel: notice the currency format applied to the “Account Balance” field.
Here’s how the field appears in the Word document: no currency format has been applied.
Numeric Picture Field Switch
The solution to this problem is to add what is called a “Numeric Picture field switch” The switch uses symbols to represent the format that needs to be applied.
To apply the switch, in your mail merge document, right-click the field that needs formatting and select Toggle Field Codes from the shortcut menu.
The field should now look something like this, with the name of your field replacing “Account_Balance”.
{ MERGEFIELD “Account_Balance”}
The switch needs to be entered just before the closing bracket. Leave a space after the field name and then type \# This begins the switch.
{ MERGEFIELD “Account_Balance” \#}
Leave another space after the # and you now need to specify the format you want to apply. I want the pound sterling currency format, so I start with a £ sign. Next I need to specify the character I am going to use to separate thousands, which will be a comma. Then using a # symbol I need to state the minimum number of characters before the decimal place that must always display. Finally, specify the number of decimal places you want display using .00
{ MERGEFIELD “Account_Balance” \# £,#.00} You can of course use a different format, so this is just given as an example.
Finally, right-click the field and select Update Field from the shortcut menu and you field should be formatting correctly.