Format currency in Mail Merge – Word 07

Topic

New Reply

I have never used Mail Merge before. I am having just one problem.
One of my merged fields is a number that I want to display as currency. I went online and hunted up how to do it.
I right-clicked on the field, selected “edit field”, clicked on “field codes” and added the “switch” to the field code so that the field codes field contained this:
MERGEFIELD “Dues1 [\# $##.00]”
The data in the field now displays as currency, but a “]” displays at the end of it!
$24.62] – like that.
How do I get rid of that trailing bracket?

Reply | Quote

Replies

I fixed it!
It turns out that adding a space before the closing bracket makes it disappear in the printed version. Stumbled on that totally by accident and don’t understand it at all, but it did the trick.

Reply | Quote

Hi orgaizer,

There are at least 3 problems with your mergefield’s coding! There should be:
1. no square brackets
2. only one backslash
3. no double quotes

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD Dues1};
. edit the field so that you get {MERGEFIELD Dues1 # $,0.00} (or whatever other numeric format you prefer – see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

Note: 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

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote