Mail Merge problem: Excel data with ROUND function retains decimals in merge

Question

BACKGROUND: I work at a school where we provide learners with a detailed list of how their personal marks makes up their term percentage in each subject. For this, each teacher uses their Excel spreadsheet with the marks and the Mail Merge function in Word. Lots of the marks need to be processed e.g. a 60 mark test will end up contributing only 10 marks towards the total - thus we use a lot of formulas.

When this data is used in Word's Mail Merge, it obviously shows these processed marks with loads of decimal places. To prevent that happening, the teachers use the =ROUND(number, num_digits) function in Excel. This works great for all the marks we round down to 0 decimal places - the merge fields in Word shows the whole number without decimals.

However, when we round to 2 decimal places, the merge field in Word shows the 9+ decimals of the original number (as if the ROUND function wasn't used at all). This even happens when one copies the column in Excel and pastes it as values, then use the values as the merge field! (e.g. I see 7.26 in the cell in Excel, but the merge field displays 7.26000000009).

To solve it, I have been formatting merge fields with the #0.0 switch, but that is definitely not an option for any other teachers - doing mail merge by themselves already took years to accomplish!

The problem lies somewhere in the integration between Word & Excel and I just can't find the source. I can email the documents to anyone who wants to have a look.

Answer 1

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:

1. select the mergefield;
2. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
3. edit the field so that you get {MERGEFIELD MyData # $,0.00} (or whatever other numeric format you prefer - see below);
4. position the cursor anywhere in this field and press F9 to update it.

Note 1: The '# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:

• # 0 for rounded whole numbers

• # ,0 for rounded whole numbers with a thousands separator

• # ,0.00 for numbers accurate to two decimal places, with a thousands separator

• # $,0 for rounded whole dollars with a thousands separator

• # "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

Note 2: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg # "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Note 3: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. # "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output with the decimal alignment occurring after the $ sign.

For more useful mailmerge tips, see: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

Answer 2

Will Microsoft Word Save the numeric picture switch for future merges? I have a mail merge that adds two extra decimal places to my currency values in Word even though in my Excel spreadsheet there are only two decimal places. I only want two decimals places just like I have in excel.

Answer 3

Provided you save the mailmerge main document after adding the switch, yes.