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?

Solution 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

Solution 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.

Comments

[Lisa]

Thanks, however, when I right click, "number" does not appear as an option.

[Xavierjazz]

Sorry I forgot a step - Click on "Format" first. I will update my answer.

[Lisa]

I see a place for decimal points but nothing for trailing zeros...

[Xavierjazz]

Set that to 2 places.

[Lisa]

Thanks. That is the format I was using, but still getting the long zeros.

[Xavierjazz]

I would change it to 3, see if that takes, and if so I would close the program, reboot it and set it to 2.

[Xavierjazz]

As I re-read your post I see that there is an amount MORE than zero on the end. Somehoe that extra numeral is being added. That's the best I can do. :)

[Lisa]

The extra zero is added when I change the decimal points to "3". This has been happening for years each time I write this letter and the same thing always happens. Therefore, I don't think rebooting with help. I usually just delete the extra zeros but that is time consuming when I have 30+ letters. I appreciate your help, though! Thanks!

[Lisa]

I see about that added numeral, odd. Maybe someone else can join in. Thanks so much for your time.

[CharlieRB]

The OP stated it is formatted correctly in Excel, but not in Word after the merge.

[Kit Johnson]

I don't agree with down-voting this just because it's not a solution @CharlieRB favours. I've used Leeroy's solution myself, and while clunky, it's no more clunky than adding a bunch of characters to each field in Word.