How do I stop a mail merge from adding zeros to the end of amounts of currency?

Question

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?

Answer 1

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

Answer 2

Try inserting another column to the worksheet, and using the formula =FORMAT(A2,"$0.00"), A2 of course would need to be changed to the column you are using. Then use the auto-fill to copy the formula down all the rows...

I dont understand why you cant get it to look right using the format menu, there is a currency option in there, that I have never had problems with.

I understand the problem isn't in excel. as OP stated, but the new column would be formatted as text by default, stopping the problems at word. Theres a hundred ways to skin a cat of course, but fixing the prob from data end saves formatting every word doc with special chars... Sorry if I offended ya Charlie, only offering my $0.02.

Answer 3

Right click on a cell with one of those numbers in it. Select Format, select number. You will see a place to limit the trailing numbers after a decimal.

Answer 4

i had this problem and worked out an easier way to rectify it.

i formatted the fields the way i wanted them to look in the mail merge then i just saved the file as a csv document then saved it as an excel again afterwards

Answer 5

My problem was not exactly the same issue as not all my numbers needed decimal places. I had used the same document and spreadsheet several times over a couple of years(printing labels for a library) and suddenly, (as in the next day) these strange decimals appeared. They did not show up in the excel sheet, only in the merge document. In my case, I changed the offending number column to text. I had to close and re-open the merge document then all was well. I believe it was some kind of glitch in the way Word was translating the numbers.