How to format a percentage in a merge field (in MS Word)?

Labels: How To: Office Tips, Microsoft Office Tips and Techniques

Posted by leecpa at 5:35 AM

May be you're like me - wants to make life easier by taking shortcuts. And when it comes to making letters, certificates, e-mails, contracts with various recipients and counter-parties with different data but with the same fields, nothing beats Mail Merge function of Microsoft Word, at least for me.

Formatting numbers can easily be done. However, formatting percentages is somehow not that easy.

For example, you have 17.12% in your Excel (used as data source) and you want it to appear in your Word document just the same.

If you're not going to tweak it, it will appear as 0.1712 or worse, if it's a product of a formula and if not rounded off, it will appear like 0.1712XXXXXXXXX and you certainly don't want that.

Word does not have a concept of percentages. However you can use the following technique to spell it out.

Option 1:
Multiply the merge field with 100 in Excel, and make the mergefield code look like this:
{ MERGEFIELD MyField \# "0%" }

Option 2:
Multiply with 100 in Word:
{ =100*{ MERGEFIELD MyField } \# "0%" }
Very important!: Remember, use Ctrl+F9 to insert field braces { }, do not type them yourself.

Option 3:

Turn on "Confirm file format conversion at open" in the General section of the Advanced "tab" of File > Options.
When you connect to the Excel workbook for the mail merge, Word will prompt you how to do that.
Click Show All, then select "MS Excel worksheets via DDE".
This will start Excel, and import the Excel values complete with their formatting.

And by the way, this is not my own knowledge, just a post from the support page of microsoft. Thanks to the contributor.

Have you tried it? Let's keep learning together. Share us your thoughts and experiences.