The magic feature of Mail Merge in the Microsoft Office suite saves users a lot of time and effort. But it’s still not perfect in the format numbers in Mail Merge. If you use a Macbook, Mail Merge will automatically get the correct format in Excel, and Windows will have to add format numbers in Mail Merge manually. I will show you how to do it and the easiest to understand example. Let’s get started!
Related posts
How to format numbers in Mail Merge
Step 1: You right-click on the inserted data and select Toggle Field Codes. Or use the following keyboard shortcut:
- Windows: Left click on the inserted data and then press Shift F9.
- macOS: Left click on the inserted data and then press Fn Shift F9.
Step 2: You will see the code in the form {MERGEFIELD_ColumnName}. Now you will insert a formula in front of the } to add the format numbers in Mail Merge. Let me give you an example to make it easier to understand; I have data like this in Excel.
| Day month Year | Number | Percent times 100 | Percent |
|---|---|---|---|
| February 14, 2021 | 5000000.789 | 96.69 | 0.9669 |
Now I insert Numeric data and want it to be in the thousands, round off the decimal to 500,001. I will insert the formula into {MERGEFIELD Number} into {MERGEFIELD Number \# #,##0} .
How to format numbers in Mail Merge is as simple as that. Depending on the data type (date, number, money, percentage …), you will choose different formulas. The formula table I have prepared below for you, I compiled according to the common data types in Vietnam.
Mail Merge number format formula table
| Format | Recipe | Eg |
|---|---|---|
| Day month Year | \@ dd/MM/yyyy | February 14, 2021 |
| Day | \@ dd | 14 |
| Month | \@ MM | 02 |
| Five | \@ 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 digits) | \# #,##0.0000 | 500,000.789 |
| Vietnamese currency | \# #,##0₫ | 500,001₫ |
| Dollar currency | \# $#,##0.00 | $500,000.79 |
| Percentage (multiplied by 100 in Excel) | \# 0% | 97% |
| Percentages (multiplied by 100 in Excel) with decimals (2 numbers) | \# 0.00% | 96.69% |
| Percentages (not multiplied by 100 in Excel) | {={MERGEFIELD_Percent}*100 # 0%} | 97% |
| Percentage (not multiplied by 100 in Excel) with decimal (2 numbers) | {={MERGEFIELD_Percent}*100 \# 0.00%} | 96.69% |
Applying the above formula table to our example, we will open Toggle Field Codes and insert the formula as shown in this figure.
For Percentage (not multiplied by 100 in Excel) ie the value in Excel is less than 1 (Example: 0.55), then you add a formula with the multiplication calculation of 100 as shown on the table. Replace Percent_with your percentage column name.
But this way depends on personality. Quite a few people do it right but still get Syntax Error (including me). If you also get the error, the only way to fix it on Excel is to multiply by 100 and then return to Mail Merge using the Percentage formula (multiplied by 100 in Excel) .
How to keep Excel’s formatting in Mail Merge
According to the information of Microsoft and some other source of their learning online, to keep the formatting of Excel in Mail Merge, then in step Confirm Data Source (Confirm Data Source) you check the Show all (Show All ) then select MS Excel Worksheets via DDE (*.xls).
But not everyone succeeds this way. I tried it on my friend’s computer and it keeps crashing. This also depends on personality. ^_^
For Office on Mac, everything becomes easier; by default, it has kept the format from Excel in Mail Merge already.
Epilogue
The Microsoft Office has many versions (2003, 2007, 2010, 2016, 2019…,). It is still on different operating systems (Windows, macOS), so no one knows which version will use Excel Worksheets via DDE (* .xls) and the calculation in Toggle Field Codes to format numbers in Mail Merge successfully or not. The rest of the formulas are 100% successful because they are simple.
Leave your comments below the article, good luck!
