Word: Change decimals from Excel data

June 18, 2009

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 mail_marge_view 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: mail_marge_number06_word displays as mail_marge_number07_word.

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.


  1. Great, it works. Thanks

  2. I’ve been looking for a solution to this for several months, thank you so much!!!

  3. wow thanks, sangat bermanfaat ^_^

  4. yes, it works.

  5. The number in the field that I’m trying to merge is a dollar amount. I want it to be a whole dollar. The above allowed me to get rid of the decimal, but how can I also make sure it still shows the $ and appropriate comma?

    For example $417,189.00 is now showing as 417189. My merge field looks like:

    Beginning balance { MERGEFIELD InvestmentPosition_BeginningValue \# 0}

    How do I edit to have the $ and comma back in?


  6. Formatting Prices with a “Numeric Picture Switch”
    Numbers don’t always automatically display with the correct formatting (currency, decimals, etc.). To fix that:

    On your original source document (before completing the merge), right-click on the field and select “Toggle Field Codes.”
    It will look something like this (where “Price” is your particular field name):
    { MERGEFIELD “Price” }
    Place your cursor before the closing bracket and type:
    \# $,#.00
    (There is a space between the first # and the $.)
    It should now look like:
    { MERGEFIELD “Price” \# $,#.00 }
    Right-click on the field name once again and select “Toggle Field Codes” to return to the original view. Your prices should now display appropriately.

    What do the Symbols Mean?
    \# Begins the “switch” which tells the document to apply formatting to the number.

    $ The character to display at the beginning of the number (in this case, a dollar sign).

    , Indicates that you want commas to designate thousands.

    # The number of number signs (#) typed after the comma indicate the number of digits that should be forced to display.

    .00 Indicates that you want to include a decimal point to two places with each number. Anytime a number to be merged doesn’t include decimals, it will display with zeros.

    SOURCE: http://iits.haverford.edu/documents/mail-merges-formatting-numbers-in-a-merge-field/

  7. Wow, thanks alot of this, works like a charm.

  8. Exactly what I was looking for and what I needed; perfect instructions. Saved me an hour (at least) cleaning up a seventy-five page number merged doc. Thanks for the help!

  9. Thanks for the very important tip.
    I am in Oman and using three decimal, also I want amount in this format like
    541,687.000. I need help to add comma after interval of 3 digits.
    Please help.

  10. this is only a band-aid. why does this happen only rarely? how if possible can we stop or control how this happens? and for me actually, it made the text bold making it stand out from the rest. what’s the solution for that?

  11. Hi Juntjoo

    Try adding MERGEFORMAT to the field code as see if that makes any difference.

    If that doesn’t work, I have no idea why it might be coming as bold, unless the original Excel data is bold.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: