Mail Merge - displaying a conditional number of decimals

[Epochend]

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!

 

[macropod]

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.

 

[Epochend]

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]

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...

 

[A Fish]

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.

 

[A Fish]

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