[Originally posted June 2012. Updated August 2018.]

In Microsoft Office Word 2003 and in earlier versions of Word, point to Letters and Mailings on the Tools menu, and then click Mail Merge Wizard. In Microsoft Office Word 2007, click Start Mail Merge in the Start Mail Merge group on the Mailings tab, and then click Step by Step by Mail Merge Wizard. Under Select document type, click Letters. Create your address labels In Word, click Mailings Start Mail Merge Step-by-Step Mail Merge Wizard to start the mail merge wizard. Choose Labels, and then click Next: Starting document. Choose Label options, select your label vendor and product number, and then click OK.

The Mail Merge feature of Microsoft Word is one of my favorite parts of the program. It is extremely powerful for creating labels and customized letters, emails, or reports. Once you learn how to use it, you can save countless hours of work. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!

Fortunately, there is a way to fix this. In fact, there are three ways. We can modify the spreadsheet, modify the Mail-Merge document, or simply modify the way the two files connect to each other. Although this last approach is little known, it might be best approach—except for one giant detail: It no longer works for Word 2016.

[Note for users of various versions of Microsoft Office: The steps presented here are for Office 2016 as of August 2018, but the steps needed for older and newer versions of Word and Excel are very similar if not identical.]

The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document.

To do this, you need to use Excel’s TEXT function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The downside is that you need to know certain formatting codes. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.

For example, the formulas TEXT(B3, “$#,##0.00”) and TEXT(C5, “M/dd/yy”) convert number and date data into textual data (in this case, “$12,345.67” and “12/01/10”).

(Tip: It is a good idea to format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. In older versions of Excel, these “numbers” could not be used in calculations. In Excel 2016, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.)

The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. Well, almost unmolested. Formatting options such as font, size, and color do not make the trip.

Format Codes for Excel’s TEXT Function

A collection of the most commonly used codes is presented at the right. A key thing to remember is that both “0” and “#” act as placeholders for digits, but “0” will force leading or trailing zeros to appear. The “#” placeholder will be replaced by a digit only if it is not a leading or trailing zero. Thus, the code “00000” will ensure that the leading zero is not truncated from New Jersey postal zip codes.

These codes for numbers, dates, and times can be used in the TEXT function or in Excel’s Custom number formats. As an extra bonus, they can also be used in the “Numeric Switches” in Word Mail-Merge Fields described in the next section.

Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. In Excel, you can use either “M” or “m” for months or minutes. In almost all cases, Excel is smart enough to figure out which units you are talking about. But Word is not so smart, and you must use the capitalized letter to refer to months. Also, Excel has an additional code, “MMMMM”, which returns a single letter abbreviation for the month (e.g., “J, F, M, A, …”).

Supplementing a spreadsheet table with a few columns featuring TEXT functions is a simple and direct approach. Just remember that these cells may look like they contain numbers, but they cannot be used in SUM or AVERAGE functions. If you need to convert them into numbers again, just use the VALUE function.

Handle with Care: The ROUND Function

Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off extra decimal places. As its name implies, Excel’s ROUND() function will permanently round a number up or down to the number of decimal places you specify. The advantage is that the rounded number is not text and can still be used for further calculations. In many cases, the ROUND() function will work well with Mail Merge, but you may want to steer clear of it because of the following reasons:

The ROUND function will not preserve the dollar sign or the thousands separator (comma)
The ROUND function will truncate trailing zeros
The ROUND function sometimes causes Mail Merge to display the wrong number of decimal places. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3.

In rare cases, the ROUND function causes Mail Merge to show a slightly different number. For example, instead of displaying 1.0014, Mail Merge showed 1.0013.

Okay. But let’s say that you can’t or don’t want to change your Excel spreadsheet. Well, there is something we can do in Word:

The second way to cajole Mail Merge into displaying numbers from Excel correctly is to modify the Word document. Specifically, this means applying the desired number format code to the Merge Fields in the Word document. (The merge fields basically tell the Word document which column in the Excel table has the desired data.) To apply a format to a field, you must include a numeric switch (formerly called a picture switch) in the field’s field code.

The first thing you have to do is to see the actual field code. Open the Mail Merge document and click the Mailings tab at the top of the window. Be sure the Preview Results button is toggled off so that you can see the Mail-Merge fields. Then right-click a Mail-Merge field (such as «Donation») and choose the Toggle Field Code option. You should now see the actual field code for that field, which is designated by curly braces as in { MERGEFIELD Donation }. Now edit the field code by simply inserting a numeric switch code to the end of the field code, as in

There are many picture codes available. Here are four examples with their respective results:

As you can see, the numeric switch codes are identical to the Excel formatting codes except that they are preceded by “#” for numbers and “@” for dates. (Note the use of quotation marks in the date code but not in the number codes.) To see more of the codes available, refer to the table below, or see the online help for “numeric switch” in Microsoft Word.

Here are some examples of how the numeric field codes work with data in a column labeled “Sales” in an Excel spreadsheet.

Numeric Switches for Mail-Merge MergeFields

In light of the fact that Word and Excel use the same formatting codes, we could not help wondering why they don’t use the same function format. For example, why not have the MergeField function look something like:

Good question. You’ll have to ask Microsoft.

Note: Before you start cursing me out, try to remember that when you add or change a numeric switch, the effect may not be shown immediately. You either have to update the field (right-click it and choose Update Field), or click the button Mailings > Preview Results. On one occasion I had to do this 2 or 3 times. If you are using the Mail-Merge Wizard, you may have to go back a step and return to see the effect of your changes. (Why? Again, you will have to ask Microsoft.)

The numeric switches in merge fields work well, but I find the process very difficult to remember. (“Is it a forward slash or a back slash?”) Also, it is very easy to make a mistake. Fortunately, for some users there is another way:<

No Mail Merge Wizard In Word