Microsoft mail merge incorrectly formatting numbers

Question

I am doing a mail merge in Microsoft Word and pulling the data from an Excel spreadsheet. The text is being inserted perfectly, but when I go to insert a number value, the number goes out to fifteen decimals places when it should only be out to two decimal places.

I have tried using the ROUND(cell,2) formula as well as Special Paste->Value, but both times Word still outputted numbers fifteen decimal places out. How can I control the formatting of these numbers in mail merge so that they display in a fixed two decimal format (i.e. $8.00, $5.20, $7.49)?

Solution 1

ROUND works for me. Please make sure you used the correct column in Word. If it still doesn't work in your office version, use the TEXT function as follows:

1. =TEXT(A1,"0.00") (assuming the numbers are in the first column and you're inserting the formula in row 1)
2. copy the formula to all rows
3. make sure that Word uses the column with the correct formula

Solution 2

Converting to text is not always the best solution if you need to do calculation in merge fields.

This is another approach, which may be of use to someone else.

In Your Word Mail Merge document you may setup the formatting conditions for a field in this manner

Right click on the field and select Toggle Field Codes

Add formatting for the field like this

A search in Word help, for "field formatting switches" will provide further examples and explains the numeric picture switch(#)

Comments

[Moses]

This worked. The text formula is not necessary, though, as it turned out I simply needed to change the cell format from number to text.

[Kit Johnson]

Thank you! I was using 'ROUND', and Word gave me weird numbers, like this 3.7000000000000002 (which displayed as 3.7 in Excel). However, 'TEXT' works perfectly. Thank you so much!