The Mail-Merge Number Format Disappointment

No more 9.48999999999999!
Microsoft has finally fixed the number format problem in Microsoft Word’s Mail Merge feature. But not completely.

When 9.5 turns into 9.48999999999999!
Has Microsoft finally fixed the number format problem in Microsoft Word’s Mail Merge feature? Some glimmers of hope — But no.

Platform: Windows & Mac
Apps: Word & Excel
Version: Microsoft 365 (nee Office 365), June 2021
Date: June 8, 2021, Updated: June 21, 2021

I was fooled! I thought that Microsoft had finally fixed that annoying number formatting problem in Mail Merge. Some early tests with the latest version of Microsoft 365 indicated that this was so. But I celebrated too soon. It turns out that the number formatting works correctly, but only in very special circumstances.

Here is what happened: I was doing some tests to see if there had been any improvement in the way Mail Merge worked. I set up an Excel workbook with columns of numbers. In each row of the numbers, I used a different number format: No decimal places in one row, then one decimal place, then two, then the Currency format, etc. Well, guess what happened? When I brought these numbers into a Word document using Mail Merge, the formats were perfect! Could it be? Had Microsoft finally fixed the number formatting problem? It seemed so, and I was ecstatic! Sort of. I quickly put together a blog post and a YouTube video to spread the good news.

But then the bad news. A few days later, I did a real-world test. After all, nobody puts a different number format on each row of a column. In this new test, I did what everybody does: the same format for each row.

And once again, you can guess what happened. In the Word document, the formats were terrible. Again, the 16 decimal places returned. What was going on?

Did Microsoft change the program again? I returned to me first test with the different format, and it still worked. After a few hours of more testing, I determined that there is some strange bug in Word 2019. If one or two of the numbers in a column have a different format from the others, the number formatting works fine in Mail Merge. Huh?

From what little I know of programming, I can’t imagine how this could happen. Maybe it’s just me. Or maybe Word does have the capability to handle number formatting correctly, but there’s a slight bug in the code. And maybe, just maybe, Microsoft will spot it and fix it.

Then, we can really celebrate. Until then, we have to use the three techniques I mentioned in another post (https://techhelptoday.com/3-ways-to-fix-mail-merge-number-formatting-in-microsoft-word/).

Mail Merge is an awesome tool, in fact, it’s my favorite part of Microsoft Word. If you use it right, it can save you countless hours of tedious work. Let’s hope Microsoft fixes this number formatting problem, and that they do it soon.

— Rich Malloy, Tech Help Today

Mail Merge in 10 Easy Steps

The Mail Merge feature of Microsoft Word is a great way to produce a large number of personalized letters or labels in a short amount of time. The process can seem daunting to a beginner, but if you break it down into a series of steps, is very easy to manage.

The Mail Merge feature of Microsoft Word is a great way to produce a large number of personalized letters or labels in a short amount of time. The process can seem daunting to a beginner, but if you break it down into a series of steps, is very easy to manage.

The Mail Merge process basically involves taking two files and merging them together. The first file is a letter, which is a basic word document. The second is a list of recipients. This list could be a table in Microsoft Word, but most often it is a worksheet in Excel. In this example, we will use an Excel spreadsheet and a simple letter that has already been created in Word.

1. Prepare the List of Recipients

The list of recipients is simply a table of names and addresses. There must be only one row of column headers at the top of the table, and each column headers should be unique. It will save some time later if you use a few standard labels in the column headers, such as, Last Name, Street, City, etc. I have prepared a very simple table of names and addresses in the file Mail_Merge_Recipients.xlsx. If you look carefully, you will note that the Postal Code column is set as text, which is why the ZIP codes appear on the left side of the cells. Unfortunately, this is mandatory: You must set the Postal Code column as text. Otherwise the leading zeros that are used in certain U.S. ZIP codes will be truncated off by Excel when it exports it to Microsoft Word. (This problem will occur even if you use the special Zip Code format of Excel.) Close the Excel file and proceed to the next step.

2. Prepare the Letter Document

You can use almost any document in Mail Merge. I have prepared a simple letter with the file name Mail_Merge_Letter.docx. The date near the top is set to update every time we create a new batch of letters, which is a good idea for a Mail Merge letter. There is a placeholder for the Inside Address and another for the salutation line. (We could also put some information from the recipient list into the body of the letter. For example, we can add the line, “I hope things are going well in X,” Where X would be substituted by the recipient’s city. But such simple-minded gimmicks impress nobody, and for this example we’ll keep things simple.)

3. Start the Mail Merge

In Microsoft Word, if you want to start a Mail Merge, you will of course go to the Mailings tab. In that tab, click the button: Start Mail Merge. A menu of possibilities appears, and easiest choice is to go to the bottom and employ the Step-by-Step Mail Merge Wizard. So far, pretty simple, right?

4. Choose the Document Type

The Mail Merge Wizard has just six steps, the first of which is the easiest. It defaults to creating a letter, which is exactly what we want. So, all you need to do is go to the next step. Click the button at the bottom right-hand corner: Next: Starting document.

5. Choose the Document

We already have our document open, so all we need to do is click Next: Select recipients. (I told you it was easy!)

6. Choose a Recipient List

Now we choose the second ingredient in our Mail Merge recipe, the list of recipients. Click the Browse button and browse to the Excel spreadsheet that we looked at earlier. A dialog box should open up, showing all of the rows and columns of our Excel spreadsheet. To the left of each row there are checkboxes which we can use to manually select who should receive our letter. Also, note that each of the column headers has a filter button, a drop-down arrow which we could use to select which groups of recipients will receive the letter. (We could also use these filter buttons to sort our letters. In some cases, we can get discounted postal rates if we were to sort the letters in the order of their ZIP Codes.) In this example will leave all our recipient selected so that everyone will receive one of our amazing letters. Click OK to close the dialog box, and then click Next: Write your letter.

7. Write the Letter

Well, our letter is pretty much already written. But we do need to add two things: the inside address, and the salutation or greeting line. Delete the text that says Inside Address and leave the mouse pointer on that line. In the Mail Merge task pane at the right, click the option: Address block. A dialog box will appear asking you to confirm that the name and address information is correct. After you click OK, a merge field code will appear in your letter. This code is distinguished by the double angle brackets that enclose it. As with all fields, Microsoft Word will replace it with some relevant information, in this case, a few lines that list the name and address of the recipient. Because we used standard labels in the column headers of our Excel spreadsheet, Word knows how to combine the name and address information in a suitable way. (If we had not used standard labels, we would now have to tell Word which of our labels corresponds to the standard labels, so that Word could assemble the address block as needed.)

Next, we have to add the salutation or greeting line. Delete the text now in the salutation and click the Greeting line option in the task pane on the right. A dialog box will appear asking you to confirm the structure of the salutation. Because this is a business letter, we need to change the punctuation to a colon. Click the list arrow at the right near the comma and change it to a colon. Then click OK.

In this step, we have added two merge fields. Be sure there is a blank line below each merge field. It looks a little cryptic right now, but that will soon change—as soon as you click Next: Preview your letters.

8. Preview the Letters

Prepare to be amazed: In this step, the merge fields have been replaced with actual data. You can use the arrow buttons to move forward or backward in your recipient list to see how each of the letters will appear. If you see any mistakes regarding line spacing or word spacing in the salutation, this is a good chance to fix that. Assuming that everything looks fine, let’s go on to the next step. Click Next: Complete the merge.

9. Perform the Mail Merge

Before we do the actual merge, it’s a good idea to save our work: Press Ctrl + S. Now, as you can see in the task pane on the right, there are two basic choices. If everything looks pretty good so far, you can take a chance and click the button: Print. This will merge our letter with our recipient list and print out X number of letters. But if there’s a mistake someplace in the letter, you may print out X number of mistakes.

If you’re the more cautious type like me, the better choice is: Edit individual letters. This creates a new document which is composed of all the individual letters that are created in the Mail Merge process. We can now go through this batch of letters and correct any mistakes. We could also add a little personalization to a particular letter, for example, “I enjoyed seeing you at the park last week.” After we make our choice, Word will ask you to confirm that you want to print all the letters, which you usually want to do.

In the new “Letters” document that appears, note that each of the letters is separated not by a Page Break, but by a Section Break (Next Page). This means that each of the individual letters are actually sections of the document. I’m not sure why Microsoft chose to break up the letters this way, but it does have an important consequence for us: If we want to print only certain letters in the document, we would specify not their page numbers, as we would usually do. Instead, we must specify the section numbers. That is, instead of entering 1, 4, 7, in the box for Pages to be printed, we would instead enter: S1, S4, S7.

Let’s assume that all the letters look fine, and we have plenty of paper and ink in our printer. What we need to do now is print the Letters document. Once it is printed, the Letters document is no longer needed. We can use the original letter document we just saved a moment ago to create a new collection of letters in just a few mouse clicks. So, close the Letters document without saving it. The original mail-merge letter document will now appear, and that should be saved for possible reuse in the future.

10. Celebrate!

Well, it turned out to be even easier than I thought. Really only nine steps! Practice this a few times, and pretty soon you’ll be able to do it all by yourself—without the wizard.

Of course, there is one more step: If we want to mail these letters, we need to print either labels or envelopes. Sorry, we’ll have to leave that for another lesson.

— Rich Malloy

3 Ways to Fix Mail Merge Number Formats in Microsoft Word

One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. Fortunately, there is a way to fix this. In fact, there are three ways.

[Originally posted June 2012. Last updated in June 2021. For a video on this topic, click: https://youtu.be/otPTkvnrPlc.]

Sidebar for Mail Merge postThe 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 are no less than three different ways to get Mail Merge numbers to appear the way they should.

[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.]

1. Modify the Excel Spreadsheet

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

Table of Excel TEXT function format codesA 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:

  1. The ROUND function will not preserve the dollar sign or the thousands separator (comma)
  2. The ROUND function will truncate trailing zeros
  3. 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:

2. Use a “Numeric Switch” 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

{ MERGEFIELD Donation \# $#,##0.00 }

There are many picture codes available. Here are four examples with their respective 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 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

Table of Microsoft Word Mail Merge Numeric Switches
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:

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

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:

3. Use a DDE Link

The above approaches are relatively simple, but if you have more than a few fields that require formatting, they can drive you into early retirement. At the very least, they require you to remember format codes that, while similar, are used in very different ways.

A much more elegant and simple solution is to have Word link to the Excel workbook via a DDE (Dynamic Data Exchange) link rather than the usual, presumably non-dynamic, linking process. That sounds a little daunting, but if you are smart enough to do Mail Merge, then DDE can a piece of cake. It is a simple two-step process, and the first step — enabling Word to open a file via DDE — has to be done only once.

[Warning: Does DDE work in Office 2016 or 2019? Dynamic Data Exchange is an old technology, and it looks like Microsoft is in the process of pulling the plug on it. Sometimes it works for me in these fairly new versions, but lately it rarely works.

One trick you could try is to be sure that the Excel data source is open before you access it from Word. As usual for Excel data sources, the table must begin on the first row of the first worksheet in the workbook file. If you do this, and have some patience, you should be able to get it to work. But, beware: It may not work all the time.]

To set up Word for DDE links, do the following:

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

That’s all for the first step, and you never have to do it again. From now on, your copy of Word can open up many different types of files, and can open these by different avenues, including DDE. Microsoft Word Mail-Merge Confirm Data Source dialog box 1The only side effect of the above is that every time you open a non-Word file with Word, the program will give you a chance to change your mind. No problem.

The second and last step has to be done each time you select a data source for your Mail Merge operation (either in Step 3 of Word’s Mail Merge Wizard or after you press the Select Recipients button in the Mailings ribbon). Relax. It is just three additional mouse clicks:

  • After you have chosen the data file you would like to use, a new “Confirm Data Source” dialog box will appear.
  • The default type of link is by OLE, but that is not what you want.
  • Microsoft Word Mail Merge Confirm Data Source dialog box 2In the Confirm Data Source dialog box, click the check box to Show all.
  • In the expanded list of file types, choose MS Excel Worksheets via DDE (*.xls). (Choose this even if you are using the newer Excel file format: *.xlsx.)
  • If asked, confirm that you are selecting the Entire Spreadsheet.

If you have already selected a spreadsheet for your Word document, you may have to select it again, this time via a DDE link. That’s it! From now on, your Excel formatting will travel over to Word Mail Merge documents fairly intact. One huge caveat here: Make sure that the data you want to merge are in the first sheet of your Excel workbook. (It took us two hours to finally figure out that DDE does not see anything but the first Excel worksheet!)

It is a shame that Microsoft has not replaced DDE with a new technology that works in a similar manner. When DDE worked, it was marvelous. Let’s hope an equally marvelous technology will appear in the near future. 


Does DDE work for you in Word 2016 or 2019? If you have a comment about that or any other issue related to Mail Merge number formatting, please let me know. Click here to post an anonymous comment.