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
521
Reaction score
48
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!
 

macropod

Microsoft MVP
Joined
Mar 2, 2012
Messages
521
Reaction score
48
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...
 
Joined
Oct 19, 2021
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!


I know this is an old thread, but I am attempting to do something very similar, except whereas OP wanted to display 3.50, I want it to display as 3.5, dropping the last zero. I am attempting to format a percentage rate that can have anywhere from one to four decimals but am struggling with how to accomplish this. For example, if the field value is 9.8000 and I want it to display as 9.8%, using {MERGEFIELD value \# 0.0###%} almost works, except that there are spaces between the number and the %, so the end result looks like 9.8 %

Any help would be appreciated.
 
Ad

Advertisements

Joined
Oct 19, 2021
Messages
2
Reaction score
0
I was able to finally figure out how to accomplish what I wanted and it wasn't all that complicated - I'd run across another post that mentioned multiplying numbers to accomplish the end goal, and realized all I needed to do was multiply the results by 10/100/1000 etc and then check to see if each was an INT as explained above.

I will leave a copy of the formula used for anyone else who may stumble upon this for reference - Though I'm already seeing how it could be improved (I'm checking to see if the original value is an INT, which isn't necessary for my purpose as I'm always wanting to display at least one decimal place - so that step could be omitted if the formula was written differently).

{ QUOTE { SET val { MERGEFIELD Data } }{ IF {REF val } = "{ =INT(val) }" "{ val \#0.0% }" "{ IF { = { REF val } * 10 } = "{ =INT ({ = val*10 }) }" "{ val \# 0.0% }" "{ IF { = { REF val } * 100 } = "{ =INT ({ = val*100 }) }" "{ val \# 0.00% }" "{ IF { = { REF val } * 1000 } = "{ =INT ({ = val*1000 }) }" "{ val \# 0.000% }" "{ val \# 0.0000% }" }" }" }" } }


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. The spaces represented in the field constructions are all required
 
Ad

Advertisements


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