How to fix number formatting errors when using Mail Merge in Word

Microsoft Word's Mail Merge feature is one of the features that users love. It works when creating custom labels, templates, emails or reports. When you know how to use it, you can save many hours of work.

However, one of the troubles of Mail Merge is that it cannot correctly format numbers from Excel spreadsheets. For example, the sales result in Excel spreadsheet is 100 but it turns into Word 99.99999999996. To fix this, we can edit the spreadsheet, edit the Mail Merge document or simply change the way the two files are linked. Although the last few people know it, it may be the best way, except that it no longer works in Word 2016.

Note : The following measures are implemented on Office 2016 but still apply on other versions of Word and Excel.

1. Use 'Number Switch' in Word

We will do the first way to edit the Word document. Here, you will use the desired number format code for the Merge Field in the Word document. The merge field will basically tell Word which column in the Excel table contains the data to convert. To apply formatting to a field, you must use Number Switch (formerly called Picture switch) in the field code of that field.

First, you need to see the actual field code by opening the Mail Merge document and clicking on the Mailings tab at the top of the window. Be sure to turn off Preview Results so you can see the Mail-Merge fields. Then, right-click on the Mail-Merge field (such as «Donation») and select the Toggle Field Code option . You should now see the actual field code (in curly braces as in {MERGEFIELD Donation}) for that field. Modify field codes by simply inserting a switch code at the end of the field code like:

 {MERGEFIELD Donation # $ #, ## 0.00} 

Wors has many image codes available, here are 4 examples with corresponding results:

 {MERGEFIELD Cost # 00.00} 01.00 

 {MERGEFIELD Sales # $ ###} $ 15 

 {MERGEFIELD Sales # $ #, ## 0.00} $ 1,500.00 

 {MERGEFIELD Date @ "MMMM d"} November 26 

As you can see, the numeric switch code is the same as Excel format code except they are followed by the "#" for the number and "@" for the date.

Note : Only use quotation marks in date code, do not use for codes.

To see more codes, see the table below or search for numeric switch codes in Microsoft Word online.

Here are some examples of numeric field codes for data in the ' Sales ' column in Excel spreadsheets.

Numeric switch for Mail-Merge merge field

How to fix number formatting errors when using Mail Merge in Word Picture 1

In fact, Word and Excel use the same format code, but do not use the same function format. For example, there is no MergeField function like the one below:

 {MERGEFIELD (Donation, "$ #, ## 0.00")} 

Note : When adding or changing the numeric switch, the effect may not change immediately, you need to update the field by clicking on it and selecting Update Field or clicking the Mailings button > Preview Results . Sometimes you have to do this 2 or 3 times. If you use the Mail-Merge Wizard, you may have to go back to the previous steps to see the changes.

2. Modify Excel spreadsheet

In an Excel spreadsheet, insert a column using the formula to convert the number or date in Excel to the text format you want to appear in Mail Merge.

1. Save time with these text formatting functions in Microsoft Excel

To do this, we will use the TEXT function in Excel. This function has the function of converting numbers or dates into the corresponding text format. However, this method has a disadvantage that you need to know certain format codes. Although these codes are identical to the code used in Excel's Custom Number formatting feature, most users rarely see them.

For example : The formulas TEXT (B3, '$ #, ## 0.00') and TEXT (C5, 'M / dd / yy') convert numbers and dates into text (in this case the conversion will become '$ 12,345.67' and '12 / 01/10 '.

Tip : You should format the box containing these formulas in another form (italic, bold, other colors) to know these numbers only in plain text. In older Excel versions, these numbers are not used for calculations, but in Excel 2016, you can use them in all formulas except for functions like SUM and AVERAGE. The advantage of formatting dates and numbers in the form of text is that when you switch from Excel to a Word Mail Merge document, there is no clutter.

Format code for TEXT function

Below is the table of codes commonly used in the Text function.

How to fix number formatting errors when using Mail Merge in Word Picture 2

Note : "0" and "#" act as placeholders for numbers but '0' forces 0 at the beginning or end, '#' will be replaced with a number if it is not top or bottom 0. Therefore, the code '00000' will ensure zero at the beginning is not removed in the New Jersey postal codes.

The codes for numbers, dates, and times can be used in Excel's TEXT or Custom number format. However, there is a difference between how these codes work in Excel and Word in Microsoft Office 2013. In Excel, you can use M or m for months or minutes. In most cases, Excel is smart enough to determine which unit you are using, but Word is not so smart, you need to use capital letters for the month, and write often for minutes. In addition, Excel has added the code 'MMMMM' to the result of a single abbreviation of the month (eg J, F, M, A, etc.)

Note: When converting these numbers in text, they are not used in SUM or AVERAGE functions, if you want, you need to convert them again using VALUE function.

ROUND function

Instead of the TEXT function, some users prefer to use the ROUND function to remove additional decimal numbers. The ROUND function will round the numbers up or down to the decimal place you specify. The advantage is that the rounding number is not in the form of text, you can still use them to calculate. ROUND function can work well in Mail Merge but rarely use them because:

1. The ROUND function will remove the dollar symbol or the thousands separator (comma).
2. The ROUND function will remove 0s at the end.
3. Sometimes the ROUND function displays wrong decimal places when switching to Mail Merge. For example, the Mail Merge document displays 4 decimal places while the Round function specifies 2 or 3 positions.

In rare cases, ROUND function also displays errors in Mail Merge for example instead of 1.0014, Mail Merge, showing 1.0013.

3. Using DDE links

The above two methods are relatively simple but you need to remember the format code and in some cases the same code but the usage is also different.

A simpler way is to link Word to Excel using DDE (Dynamic Data Exchange) links. It sounds complicated, but if you can merge mail then DDE cannot make it difficult for you. You only need to do two steps and the first step is to activate Word to open the file via DEE (this step only needs to be done once).

As mentioned above DDE does not work in Office 2016. Dynamic Data Exchange is an old technique and it seems that Microsoft is in the process of giving up it. Sometimes it works in Office 2016 and sometimes not. The trick for it to work in Office 2016 is that the Excel data source must be opened before accessing it from Word. And in Excel, the table must be started in the first row in the first worksheet in the file.

To set up a DDE link in Word 2013, follow the steps below:

Step 1 . Click on File> Options .

Step 2 . Click on the Advanced tab on the left and scroll in the General section.

Step 3 . Click Confirm file format conversion box on open .

This is the first step and you will not have to do it again. From now on, copies of Word will be opened in many different file types and opened with other paths including DDE. The disadvantage of this method is that every time you open a file other than a Word document in Microsoft Word, the program will let you change your choice.

The second step is the last step you need to do each time you select the data source for Mail Merge (before Step 3 of the Mail Merge tutorial or after clicking the Select Recipients button in the Mailings ribbon). Follow the steps below:

Step 1 . After selecting the data file you want to use, the Confirm Data Source dialog box will appear.

How to fix number formatting errors when using Mail Merge in Word Picture 3

Step 2 . The default type is OLE but you can change it to another type. In the Confirm Data Source dialog box, click Show all . In the expanded file type list, select MS Excel Worksheets via DDE (* .xls) . (Select this option if using a newer Excel file format * .xlsx). If asked, confirm that you select Entire Spreadsheet .

How to fix number formatting errors when using Mail Merge in Word Picture 4

If you have selected the spreadsheet for the Word document, you need to select it again, this time via the DDE link. From now on, Excel format will be transferred to Word Mail Merge document quite intact.

Warning : Make sure the document you want to merge must be in the first sheet of the Excel document.

Above are ways to fix formatting errors when using Mail Merge in Word. I wish you all success!