Mail merge: Excel spreadsheet data to MS Word doc

[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

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

[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!

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

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