Word: Change decimals from Excel data

When you’re using an Excel spreadsheet for mail merge data in Word, any numbers — such as those for currency — come in in the ‘native’ Excel number format even if you’ve changed the number format for the relevant cells in Excel. From the experimentation I did, a number such as 13733.735 displayed in an Excel cell as 13733.74 but came into Word as 13733.735000000001 — not a very friendly number to work with (and what’s with the 12 decimal places, and where did that 1 come from?).

Here’s how it looks in Excel:

And here’s how it came into Word:

If you want the number in the mail merged document to be displayed differently, e.g. to two decimal places, then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

1. Insert the mail merge field for the number into the Word document as normal. In this example, the merge field is called Amount.
   
2. Right-click on the mail merge field, and select Toggle Field Codes.
3. Put your cursor after the closing “ of the field name (e.g. “Amount” and before the closing } and add a space.
4. Type in the switch: \# 0.0x
   
5. Right-click on the mail merge field again, and select Toggle Field Codes.
6. Save the document. The next time you run a mail merge, the number will be in the format you entered at Step 4. (You can quickly test it works by clicking the View Merged Data button on the Mail Merge toolbar.)
   

This example shows just one number format switch — experiment with other combinations to get the number format you want. For example, \# 0 for whole numbers; \#0.00x for three decimal places etc.

For example: displays as .

You can get more information from Word’s online Help for all sorts of other things you can do with these field switches — search for numeric picture switch in the Help and expand the Picture Items icon at the end of that topic.

Has this tip helped you? saved you time? saved your skin? You can thank me by clicking on the cup and buying me a coffee. (An E-Junkie shopping cart page will open where you can pay for my coffee via PayPal.)