Mail Merge - displaying a conditional number of decimals


Joined
Oct 23, 2017
Messages
2
Reaction score
0
I'm creating some mail merge letter templates which include a field for a currency amount, and I'm looking for a way to conditionally change the amount of decimals shown depending on what the value is. I need it so whole numbers don't display any decimal places, but anything with pence shows 2, as per the following:

3.00 shows as 3
3.78 shows as 3.78
3.50 shows as 3.50 (not 3.5)

The merge data doesn't come from Excel, and I can't edit the values at the source, so the formatting would have to be applied to the merge field itself on Word. I haven't been able to find how to do this anywhere - would be very grateful for any help!
 
Ad

Advertisements

macropod

Microsoft MVP
Joined
Mar 2, 2012
Messages
503
Reaction score
46
If you want to generate a mailmerge where whole values don’t display '.00' decimals, you could use a field coded as:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}={=INT(Val)} {Val \# ,0} {Val \# ,0.00}}}
or:
{QUOTE{SET Val «Data»}{IF{REF Val}={=INT(Val)} {Val \# ,0} {Val \# ,0.00}}}
where 'Data' is the field name.

Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required.
 
Joined
Oct 23, 2017
Messages
2
Reaction score
0
If you want to generate a mailmerge where whole values don’t display '.00' decimals, you could use a field coded as:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}={=INT(Val)} {Val \# ,0} {Val \# ,0.00}}}
or:
{QUOTE{SET Val «Data»}{IF{REF Val}={=INT(Val)} {Val \# ,0} {Val \# ,0.00}}}
where 'Data' is the field name.

Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required.
Thanks for your response - I was able to get the first version to work perfectly as intended. 2nd version was always showing 2 decimals for some reason (I think Word was doing some sort of auto-conversion after I interested the merge field), but no issue since the other method works just fine. Thanks again!
 
Ad

Advertisements

macropod

Microsoft MVP
Joined
Mar 2, 2012
Messages
503
Reaction score
46
2nd version was always showing 2 decimals for some reason
They're the same field code. «Data» and {MERGEFIELD Data} are just different expressions of the same field...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top