• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7064
  • Last Modified:

Mail Merge number formating

I am doing an MS Word mail merge. The data is coming from an Excel spread sheet.  The merge is working, but I have a formatting problem with two fields.  In Excel I have a cell with a formula that I have adjusted the format to display 3 decimal places. In Word it is showing as the true answer of many decimal points.  I need to display only 3.  The other is a currency field that looks fine in Excel, but in Word it does not show commas.
1 Solution
When Word reads a value from Excel, it read the underlying value, not the formatted value.
To get formatted output in MailMerge, you can format the field in Word:

In your mailmerge document, in the first label, click on the number field and press Shift+F9 to show the underlying field.
Then add the formatting code at the end, inside the braces.
It should look like this (formatting for an integer with three decimal places)
{ MERGEFIELD fieldname \# 0.000 }
or like this is you want currency with commas and currency symbol:
{ MERGEFIELD fieldname \# $###,##0.00 }

Then press Shift+F9 again, and Update the labels to propagate the new field definition to the other labels (you may need to click preview results off and on to get the display correct).

There's more information on the number (and other) formats here: http://office.microsoft.com/en-us/word-help/insert-and-format-field-codes-in-word-2010-HA101830917.aspx#BM8
leewallinAuthor Commented:
Perfect answer.  It worked

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now