When you need to personalize contracts and other documents, Word’s Mail Merge is the go-to tool. However, number fields don’t always show up in the format you want. I’ve put together a set of formulas to help you format numbers in Mail Merge correctly, and I’ll also show you how to keep the number format from Excel.
Basic number formatting in Mail Merge
To change how a merge field displays its number, you need to switch it to Field Codes view. Let’s go through these basic steps first.
Step 1: Right-click the merge field and select Toggle Field Codes. You can also click the field and press Shift + F9.
Step 2: By default, the field code looks like {MERGEFIELD field_name}. To add a number format to the field, you add a format code after field_name. As an example, let’s say I have an Excel file with these columns.
| Date | Number | Percent x 100 | Percent |
|---|---|---|---|
| February 14, 2021 | 500000.789 | 96.69 | 0.9669 |
In Mail Merge, to display the number 500000.789 as 500,001, I change the field code to {MERGEFIELD field_name \# #,##0}.
That’s the basic way to change a number format in Mail Merge. Next, let’s look at other formats such as dates, percentages and decimals.
Mail Merge number format formulas
The table below lists common formulas for formatting numbers in Mail Merge. Pick and combine them depending on what you need.
| Target | Formula | Display |
|---|---|---|
| Day/month/year | \@ dd/MM/yyyy | 14/02/2021 |
| Day | \@ dd | 14 |
| Month | \@ MM | 02 |
| Year | \@ yyyy | 2021 |
| Hours and minutes | \@ HH:mm | 17:30 |
| Hours, minutes, and seconds | \@ HH:mm:ss | 17:30:15 |
| Thousands separator | \# #,##0 | 500,001 |
| Thousands separator with 3 decimal places | \# #,##0.000 | 500,000.789 |
| Vietnamese currency | \# #,##0₫ | 500,001₫ |
| Dollar currency | \# $#,##0.00 | $500,000.79 |
| Percentage (multiplied by 100 in Excel) | \# 0% | 97% |
| Percentage (multiplied by 100 in Excel) with 2 decimal places | \# 0.00% | 96.69% |
| Percentage (not multiplied by 100 in Excel) | {={MERGEFIELD field_name}*100 \# 0%} | 97% |
| Percentage (not multiplied by 100 in Excel) with 2 decimal places | {={MERGEFIELD field_name}*100 \# 0.00%} | 96.69% |
If you run into errors with the Percentage (not multiplied by 100 in Excel) formulas, the easiest fix is to multiply by 100 in Excel and use the other percentage formula instead. What matters is that the number shows correctly in Mail Merge. Going back to the original example, here is how I add a number format formula to the field code of every field.
Use number formatting from Excel
Change the Confirm Data Source setting
On Windows, after you select the data source, choose MS Excel Worksheets via DDE (*.xls) in the Confirm Data Source dialog box.
On macOS, Mail Merge in Office for Mac uses the number format from Excel by default.
Add a line of text below the field name
If the formulas still give you trouble, try this workaround. Insert a new row right below the header row in Excel, then type any text into the number columns. Mail Merge will then treat those columns as text and keep the values exactly as they appear in Excel.
Conclusion
I hope these methods help your Mail Merge work go smoothly. Leave a comment below to share your results or ask any questions. Good luck!
