3 ways to fix numeric formatting errors when using Mail Merge in Microsoft Word

One of Mail Merge's long-standing annoyances is the inability to correctly format numbers in Excel. Fortunately, we always have a way out. Here are 3 ways to fix number formatting errors in Mail Merge.

Microsoft Word 's Mail Merge is one of the most popular features. It creates labels, letter templates, emails or reports extremely effectively. When you know how to use it, you can save hours of work. Unfortunately, mastering all the uses of Mail Merge can be a headache.

One of the problems with the mail merge feature is that it cannot properly format numbers in Excel spreadsheets. For example, the sales result in Excel is now 100 but in Word, it suddenly turns into 99.99999999996!

Fortunately, fixing this error is not difficult. In fact, we have 3 ways to handle number format errors when using Mail Merge . You can edit spreadsheets, Mail-Merge documents or change the way the two files are connected. Although this solution is little known, it may be the best option - except that it no longer supports Word 2016.

Note: Although the following steps are done in Office 2016 , it still works on other versions of Word and Excel.

1. Use "Numeric Switch" in Word

The first way for Mail Merge to format numbers from Excel correctly is to edit a Word document. Specifically, you will apply the desired number format code to the merge fields - Merge Field in the Word file. (Basically, merge fields notify the Word document which column in the Excel spreadsheet contains the data to handle.) To apply formatting to a field, you must include a Numeric Switch (formerly known as Picture Switch ) in the field's code box.

The first thing you have to do is know the actual field code. Open the Mail Merge document and click on the Mailings tab at the top of the window. Make sure the Preview Results button is turned off so you can see the Mail-Merge fields. Then right-click in a Mail-Merge box (eg " Donation" ) and select Toggle Field Code . You will see the code for that field, identified by curly braces, for example: {MERGEFIELD Donation} . Now edit the code for this field just by inserting the numeric switch at the end of the code as follows:

The software provides many image codes. Here are 4 examples that correspond to the result:

As you can see, the Numeric Switch code is identical to the Excel format code, except that before the number is "\ #" and the date is "\ @". (Note, use quotation marks in the date code but not in the numeric code.) To see more code, go to the table below or visit the online help for "numeric switch" in Microsoft Word.

Here are some examples of how numeric field codes handle data in the "Sales" column in Excel spreadsheets.

Numeric Switch for Mail-Merge merge fields

In fact, Word and Excel use the same format code, so why don't they use the same Format feature ? For example, why not use the MergeField function like this:

This is really the concern of many people and perhaps only Microsoft knows the answer.

Note: Remember that when you add or change a Numeric Switch, the results will not appear immediately. You must update the field (right-click on it and select Update Field ), or click the Mailings button > Preview Results . Sometimes, you have to repeat 2 to 3 times this operation. If you are using the Mail-Merge Wizard, you may need to take a step back and undo to see the impact of the changes you have made.

Numeric switches work well in the mixing field, but their use is a bit difficult to remember and easy to make mistakes. If you feel the same way, try one of the next two ways.

2. Edit Excel spreadsheets

This is a way to help you avoid the error of formatting errors when using Mail Merge. In an Excel spreadsheet, insert a column containing a formula that converts numbers or dates in Excel into the text format you want to display in the Mail Merge document.

To do this, you need to use Excel's TEXT function. It allows users to convert numbers or dates into equivalent text format exactly the way the user wants. The downside of this solution is that you need to know the formatting code. Although these codes are identical to the codes used in the Custom Number format feature, few Excel users know of their existence.

For example, the TEXT (B3, "$ #, ## 0.00") and TEXT (C5, "M / dd / yy") formulas convert numeric and date data into text data (in this example, they are "$ 12,345.67" and "12/01/10").

Tip: You should format the cells containing these formulas in different styles, such as italics or some other strange color, to remind you that the numbers are just text. In older versions of Excel , these "numbers" cannot be used in calculations. In Excel 2016, you can use them in all formulas, except for sum functions like SUM and AVERAGE.

The advantage of numeric and date formats such as text is that the text is converted from Excel into a document that uses Mail Merge without distortion. The format options such as font, size, color are unchanged.

The format code for Excel's TEXT function

Below is a collection of the most commonly used codes on the right. The thing to keep in mind is that both "0" and "#" are placeholders for the numbers, but "0" will force the number 0 to appear in the first or last position. "#" Will be replaced with a number if it is not a leading or final zero. Therefore, the code "00000" will ensure the leading zero is not truncated from the New Jersey postcodes.

These codes for numbers, dates and times can be used in the TEXT function or Excel's custom number format (Custom). In addition, they can also be used in "Numeric Switches" in Word Mail-Merge Field.

Note, there is little difference between how the code works in Excel and Word in Microsoft 2013. In Excel, you can use "M" or "m" for months and minutes. In most cases, Excel is smart enough to know which unit you are referring to. But Word is not "smart" like that. You must use uppercase letters to indicate the month. Besides, Excel also has an additional code, "MMMMM", which returns only one abbreviation for the month (for example: J, F, M, A, ...).

Adding a few columns containing the TEXT function to the spreadsheet is a simple solution to fix numeric formatting errors when using Mail Merge. You just need to remember that these cells may look like numbers but cannot apply the SUM and AVERAGE functions to them. If you need to convert them to numbers again, you only need to use the VALUE function.

Be careful when dealing with the ROUND function

Instead of the TEXT () function, some people prefer to use the ROUND () function to reduce the number of decimal places. As its name implies, Excel's ROUND () function by default will round up or down the decimal number at the user selected position. The advantage of this approach is that the rounded numbers are not textual and can still be used for calculations. In many cases, although the ROUND () function works well with Mail Merge, you may want to avoid it for the following reasons:

1. The ROUND function does not hold a dollar sign or a comma separating thousands or more.
2. ROUND will truncate zeros at the end.
3. The ROUND function sometimes causes Mail Merge to display decimal errors. For example, the Mail Merge document displays 4 decimal places while ROUND has selected 2 or 3.

In some rare cases, the ROUND function causes Mail Merge to deviate a bit. For example, instead of 1.0014, Mail Merge now shows 1.0013.

3. Use DDE Link

The above two are relatively simple, but if there are many fields that need formatting, they can be frustrating early because you need to remember the format code and although the code is the same, they can be used in different ways.

A light, easy solution is to link Word to the Excel workbook via DDE (Dynamic Data Exchange), which is better than the normal fixed binding process. It sounds complicated, but if you've mastered Mail Merge, DDE is just a "little thing". It is a simple two step process. As a first step, you just need to turn on Word to open the file via DDE. This process only needs to be done once.

Does Office 2016 support DDE? Dynamic Data Exchange is an old technology. Sometimes it works fine but sometimes it doesn't work in Office 2016. You should open the Excel data source before accessing it from Word. Normally with Excel data sources, you must start importing data from the first row of the worksheet. If you're patient enough, you can use DDE in Office 2016).

To set up Word 2013 for DDE links, follow these steps:

• Click File> Options .
• Click the Advanced tab on the left and scroll down to the General section .
• Check the box Confirm file format conversion on open .

From now on, you never have to do the above. You can now open a copy of Word in a variety of formats and paths, including the IDE. The only effect is that every time you open a non-Word file, the program will give you a choice.

The second and final step is to select the data source for the Mail Merge process (in step 3 of the Mail Merge Wizard in Word or after clicking the Select Recipients button in the Mailings ribbon ). Now you only need to perform 3 more clicks.

• After selecting the data you want to use, a new " Confirm Data Source " dialog box will appear.
• The default link type is OLE, but not the one you want.
• In the Confirm Data Source dialog box , click the Show all box .
• In the expanded file type list, select MS Excel Worksheets via DDE (* .xls). (Use this option even when using the newer Excel file format: * .xlsx.)
• If requested, confirm that you are selecting the Entire Spreadsheet .

If you selected the spreadsheet for the Word document, you may have to select it again, but this time through the DDE link. It's done! From now on, your Excel format will be applied correctly on Word Mail Merge documents and you only need to remember to put the data you want to merge in the first workbook of the Excel workbook.

Above is how to fix the number format error in Mail Merge . Hope they are useful to you. If there's any other way, don't hesitate to share with us.