Answer Box: Numbers don't merge right in Word

By Connie Miller

Your mail merge appears to be working just fine, but suddenly you notice that prices, dates and other numbers don't look right in your merged documents. Read on to learn how to avoid this particularly costly problem.

APPLIES TO
Microsoft Office Word 2003 Microsoft Word 2002 Microsoft Office Excel 2003 Microsoft Excel 2002

A question from your comments

My data is in an Excel worksheet. When I do a mail merge, the prices in the Price column don't retain their appropriate money formats. Help!

Advice from the Answer Box

Little frustrating things like numbers not merging correctly can mess up an entire mail-merge project.

Fortunately, you can use a few different approaches to get any numbers — currency, percentages, dates, zip codes with initial zeros — to come out looking just the way you want. The table below lists the approaches and gives my advice about when to use them. Following the table, you'll see more specifics on exactly how each approach works.

APPROACH	ADVICE
Connect to the Excel worksheet using Dynamic Data Exchange (DDE)	Sounds techy and complicated but isn't Probably the slickest overall approach By far the best approach if the spreadsheet you're merging from already exists and has a lot of numbers in it
Muck around with field codes in Word	Much, much easier than it sounds Great approach if the worksheet you're merging from has just one or maybe two number columns, such as prices or dates Excellent way to learn a little more about how fields work in a mail merge
Format as text the Excel worksheet cells that contain numbers	Good approach if you're creating a new worksheet for the merge, and you won't be doing any calculations using the numbers the worksheet contains

USE DYNAMIC DATA EXCHANGE

Dynamic Data Exchange (DDE) is one method available in the Microsoft Windows® operating system for transferring data between applications. It uses shared memory to exchange the data. If you use other data transfer methods, you run into a problem: Data from the worksheet is transferred in the native format in which Excel stores it rather than with the formatting that you applied in the worksheet cells.

To choose the DDE data transfer method when you're connecting to an Excel worksheet during a mail merge, all you have to do is select one check box in Word:

On the Tools menu, click Options, and then click the General tab.
Click Confirm conversion at Open.

At the step in the mail-merge process when you connect to your data file, after you locate the Excel worksheet you want to connect to, the Confirm Data Source dialog box opens. Click MS Excel Worksheets via DDE (*.xls), and then click OK. In theMicrosoft Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information you want to merge, and then click OK. The numbers from the Excel spreadsheet will now look exactly the same in your merged documents as they look in the worksheet cells.

NOTE It's probably a good idea to turn off the Confirm conversion at Open option after you finish your mail merge. Otherwise, you might be prompted to confirm your data source at times when that's the last thing you want to worry about.

MUCK AROUND WITH FIELD CODES IN WORD

Because the DDE approach is so slick, you might be tempted to stop reading right here. It's worth pressing on, though, because learning a little bit about field codes can give you a lot more control over your mail-merge results.

Format currency and other numbers by using field codes

Let's start with an example. Say you insert a Price field into a form letter that you're preparing for a mail merge. In the main document, it looks something like this, where «Price» is the field:

The gizmo you ordered will cost «Price».

Press ALT+F9, and you'll see the code behind the field. That code will look like this:

The gizmo you ordered will cost { MERGEFIELD "Price" }.

You can control the formatting of the prices in that field just by typing a few additional characters (that is, by adding a formatting switch) inside the braces.

To include:

a dollar sign
four digits by default, and a space if the number you're merging has only three digits
two decimal places
and a comma between the first and second numbers

this is what you type (shown in bold) in the field code:

{ MERGEFIELD "Price" \# $#,###.00 }

When you finish typing, press ALT+F9 to stop looking at field codes. Now when you merge, all of your prices will be formatted exactly the way you want. (You can use this same approach with numbers other than prices.)

Here's a breakdown of the elements in the switch we just used:

The name of the field that you inserted into your main document. It corresponds to a column in your Excel worksheet.
Backslash, which starts the formatting switch.
Definition of the switch — in this case, to format numbers.
Characters that you want to include — for example, a $ that appears before each price.
The maximum number of digits. If there are fewer digits in a number, Word leaves a blank. Type commas where you want them to appear in the number.
Decimal point, which you type where you want it to appear. The zeros specify the maximum number of digits after the decimal point. If there are fewer digits, Word puts in a zero.

In the See Also box, you will find a link (called Numeric Picture field switch) to more information about formatting numbers by using a switch.

Format dates by using field codes

You can also use a formatting switch to get dates from a Date column in your spreadsheet to look exactly the way you want in your merged documents. If you insert a Date field into the main document and then press ALT+F9, you see this:

{ MERGEFIELD "Date" }

To get all the dates in the merged documents to have the format February 18, 2008 (regardless of how the dates are formatted in the worksheet cells), you can add this formatting switch (shown in bold) to the field code:

{ MERGEFIELD "Date" \@ "MMMM d, yyyy" }

In the See Also box, you can find a link (called Date-Time Picture field switch) to more information about formatting dates by using a switch.

FORMAT CELLS IN AN EXCEL WORKSHEET

This approach might be just the ticket if you're creating a new Excel worksheet to use with a mail-merge project. In some ways, it's the most straightforward approach because you can set up the dates, percentages, and other numbers in your worksheet just the way you want them to look in Word after the merge. Every time you merge, they'll look that way.

Number formatting problems in a mail merge occur when the cells that contain the numbers in the Excel worksheet are formatted as Number, Currency, Date, Percentage, and so on. The solution for getting numbers to behave properly when you merge is to format as Text the cells that contain numbers. When cells are formatted as Text, you can type your numbers in the cells exactly the way you want them to look (with dollar signs, zeros, and so forth). Then they will merge exactly as you type them.

To set cell formatting in Excel, select the column that contains the cells you want to set.
On the Format menu, click Cells, and then click the Number tab.
Under Category, click Text.

Mix of Knowledge

Numbers don't merge right in Word - Word - Office.com