Excel – How to stop a mail merge from adding zeros to the end of amounts of currency

mail-mergemicrosoft wordmicrosoft-excel-2003

I am using an excel spreadsheet as my data source in a Word document mail merge. I have formatted the amounts in excel as currency with 2 decimal places. When I complete the merge, the amounts have approximately 13 zeros added to the end. See example below. Not sure if this is a Word problem or Excel and could really use some helping fixing this. Below is the example of what I'm getting.

The current amount due on you water bill is $94.120000000000005. The amount PAST DUE is $40.280000000000001. Please pay a MINIMUM of $40.280000000000001 by 4:30pm on Monday, October 6th, 2014, or the water supply will be turned off.

How can I get Word to stop adding the extra zeros?

Best Answer

There are a couple way to handle this.

  1. Use Dynamic Data Exchange
  2. Adjust the merge field properties in Word
  3. Format the numbers as text in Excel

Since you have already made the merge connection, it seems to me you should start by trying to adjust the field properties in Word. To do this in Word, press Alt+F9 to show the field values. It will look like this (the word "Price" is just an example, yours will be different);

{ MERGEFIELD "Price" }

You can add formatting to these numbers by adding a switch and commands as follows. 

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

The \# tells Word you are going to give it number formats. The $ tells Word to use a dollar sign before the number. The #,### tells Word the maximum number of digits. The .00 tells Word to use two decimal places.

Once you've made your edits, press Alt+F9 again to turn off field code editing.

Source: Answer Box: Numbers don't merge right in Word

Related Question