Table of Contents
To personalize contracts and documents, Word’s Mail Merge feature is the most used tool. However, numeric data fields are sometimes not displayed in the correct format we want. Lucid Gen has compiled a set of formulas to help you format numbers in Mail Merge more correctly. I also show you how to get the number format in Excel.
Basic Mail Merge number format
To intervene in the number format of an information field displayed on Mail Merge, we must view it in Field Codes mode. Please familiarize yourself with these basic steps first.
Step 1: Right-click on the information field and select Toggle Field Codes. You can also use keyboard shortcuts by left-clicking on the information field and pressing the Shift F9 key combination.
Step 2: The default Field Codes mode will be {MERGEFIELD field_name}, to add a number format to that information field, we need to add a format code after field_name. For a more specific example, I will have an Excel file with column names as follows.
| Date | Number | Percent x 100 | Percent |
|---|---|---|---|
| February 14, 2021 | 500000.789 | 96.69 | 0.9669 |
On Mail Merge, to format the number 500000,789 to 500,001, I will change the Field Codes to {MERGEFIELD field_name \# #,##0}.
So, we know how to change the number format in basic Mail Merge. Next, we will learn about other formats, such as dates, percentages, and decimals.
Use the Mail Merge number format formula
The table below shows standard formulas for formatting numbers in Mail Merge. Please use them flexibly, depending on your goals.
| 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 |
| Number in thousands | \# #,##0 | 500,001 |
| Thousands with decimals (3 numbers) | \# #,##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 decimals (2 numbers) | \# 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 decimals (2 numbers) | {={MERGEFIELD field_name}*100 \# 0.00%} | 96.69% |
Suppose you encounter an error with the Percentage (not multiplied by 100 in Excel). In that case, it is best to multiply by 100 in Excel and then use another formula, as long as we have the correct number format on Mail Merge. Going back to the original example, Lucid Gen will insert a number format formula into Field Codes for all information fields like this.
Use number formatting from Excel
Set up Confirm Data Source
For Windows computers, after you have selected the data source, in the Confirm Data Source dialog box, choose MS Excel Worksheets via DDE (*.xls).
For macOS computers, Mail Merge in the Office suite for MacBook will automatically take the number format available in Excel by default.
Add a line of text below the field name
If you have difficulty formatting numbers in Mail Merge using formulas, you can overcome them this way. Insert a blank line below the field name line, then fill in any text in the number columns. This makes Mail Merge recognize that this is a text column and will take the correct format as in Excel.
Conclusion
With the above methods, Lucid Gen hopes they will help you complete your work with Mail Merge smoothly. Please leave a comment below this article about your results or questions. Good luck!
