Member Avatar for pamelaanne

I've created an Excel spreadsheet that I use to incorporate data into a Microsoft word document. (Word/Excel 2003) When I do the mail merge and bring in the data, Excel takes a few of the data cells and adds zeroes after the decimal. I've attached a .jpg image of the screen shot during the mail merge.

For example what should have been ACTUAL WAGES 890.40 has been changed to 890.39999999998. I've tried formatting the cells as General, Text, Dollar, and using number formatting limiting it to 2 decimal places, but I still get this wierd error, and only in random cells. This is data I originally entered by hand to create the excel spreadsheet. Any ideas how to stop this? It's actually changing employees salaries!

Thank you,
Pamela

excelbug.JPG 94.82 KB
  • 4 Contributors
  • 4 Replies
  • 297 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by random1970

Dani AI

Generated

Brief expert note: the long tails like 890.39999999998 are not a Word bug so much as how computers store decimal numbers. Excel keeps numeric values in binary (IEEE 754) so some decimal cents can’t be represented exactly; formatting only changes the display, not the stored value. That is why a merge can sometimes reveal the full stored value even when the sheet looks fine.

Safer, non-destructive fix (recommended for payroll):

  1. Add a helper column next to the problem column.

  2. In the first helper cell enter a rounding formula, for example:

    =ROUND(C2,2)

  3. Fill the formula down, copy the helper column, then Paste Special → Values back over the original column to commit the rounded cents as the actual cell values. Delete the helper column and run the merge. Always keep a backup before you overwrite data.

Other options and cautions:

  • As and found, converting values to text will stop the artifact but can be tedious and removes numeric behavior.
  • If you prefer a merge-time fix, ’s field-formatting approach (formatting the merge field in Word) is a non-destructive alternative.
  • Excel’s “Set precision as displayed” will force stored values to match what you see, but it permanently changes all numbers in the workbook—do not use it on live payroll files without a tested backup.

Quick test: always try a 5–10 record merge after any change. For money or legal documents, commit cents into the workbook (round + paste values) so merged output matches the source exactly.

Recommended Answers

All 4 Replies

Member Avatar for MidiMagic

Questions:

- Is this happening in the Excel spreadsheet, or only in the mail-merge?

- Are these cells typed in, or calculated by formula?

- Are any macros acting on the cells?

Remember that formatting a cell does not change the value in the cell, but just how it is displayed.

One suggestion is to enter the values in those cells as text, not a number. Put an apostrophe (single quote) in front of the value, to force it to be text.

Member Avatar for pamelaanne

Hello,
It was only happening in the merge. The cells were hand entered, not calculated. I formatted the cells to text, and that fixed the problem. Unfortunately I had to go back through and reenter all the decimal values, but it was worth it to have a document that will now merge without problems.

Thanks for the help!

Member Avatar for ffej19

Ok we had the same problem

If we modify the formula to { MERGEFIELD variable \# ##0,00} (depending on how many decimal you need) it works, but us we needed different numbers of decimal (0,0 0,00 0,000) because our excel field is set to "standard"

here's what we found,

{ MERGEFIELD variable } seems to be for "text fields"
{ =variable } for "numeric fields"

so we've modified in our form all numeric variable to { =variable } instead of { MERGEFIELD variable } and everything works just fine and it reflect exactly what we see in the Excel document.

Member Avatar for random1970

Here's another option. It's a spin on Midimagic's solution, but doesn't require you to retype the values as text. It's pretty adaptable too, depending on what you need.

To fix this, modify the Excel document. For example, if your data is in column C, you have your header in C1 and Cell C2 has 839.40 in it (but the merge for this record is resulting in 839.3999999999 for no apparent reason, then

Insert a column next to column C (column D), and put in this column the formula "=FIXED(C2,2). Fill down. Change the merge document to refer to this column instead of the first.

Alternately, creating the reference column as text also works. e.g. Try adding another column - column E. in the top data cell in this column put in the formula "=text(C2,"$0.00). Fill down. Change the merge document to refer to this column instead of C. The merge will then result in a currency text value, shown to two decimal places.

Attached are two files, zipped together: Sample Merge Data File 1, and Sample Mail Merge File 1. Save the zip file to your desktop, then unzip. Open the Word doc, and link to the Excel file as your data source. Scroll through the records - you'll see what I mean.

As the first column of the Excel file is randomly generated and formula driven, the answers can have many decimal places. The two columns referring to it modify the value for display in the merge document.

Sample_Mail_Merge_File_1.zip (5.52 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.