Microsoft Office Forums - View Single Post - How to Suppress non-monetary 0

macropod · 12-03-2013, 07:56 PM

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 MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it.
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.

Handling cells that may be empty, as distinct from holding 0 values that you want to display requires a different approach. You can test the contents of a mergefield for text/nulls vs numbers by doing the following:
• select the field and Press Ctrl-F9 twice to enclose it in a pair of fields, thus { { «MyData» } };
• between the last pair of field braces press Ctrl-F9 twice to generate another pair of fields so that you end up with: { {SET Val «MyData» }{ { } } };
• before the new last pair of field braces press Ctrl-F9 to generate another field so that you end up with: { {SET Val «MyData» }{ { }{ } } };
• fill in around the new field braces so that you end up with: {QUOTE{SET Val «MyData»}{IF{REF Val}={=Val} {=Val \# $,0.00}}} (you can use any numeric format - see above);
• position the cursor anywhere in this field and press F9 to update it.