Formatting decimals ONLY for non-whole numbers in MailMerge?

Albie Aware 6 Reputation points


Our organization needs to send thank you letters for donations using Mail Merge. Most of the donations are whole numbers ($100), but occasionally some donations include cents ($100.75). We want donations with whole numbers to NOT show any decimal places ($100 NOT $100.00). However, donations including cents still need to show the two decimal places. Additionally, we need to include commas as thousand separators.

I cannot figure out what code I need to put in for this.

Any help is appreciated! Thank you.

Word Management
Word Management
Word: A family of Microsoft word processing software products for creating web, email, and print documents.Management: The act or process of organizing, handling, directing or controlling something.
908 questions
0 comments No comments
{count} vote

2 answers

Sort by: Most helpful
  1. Paul Edstein 171 Reputation points

    You could use a compound field coded as:

    {QUOTE{SET Val {MERGEFIELD MyField}}{IF{=INT(Val) # 0.00}= {REF Val} {=INT(Val) # $,0} {=Val # $,0.00}}}

    where 'MyField' is your mergefield's name.

    Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); 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.

    2 people found this answer helpful.

  2. Charles Qi_MSFT 966 Reputation points

    Hi, @Albie Aware

    You can select cell of the donation with whole numbers in Excel then go to Home->Number and click following icon:

    And when you set the format of each cell as currency, the value will get thousand separators.

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in email-notifications to enable e-mail notifications if you want to receive the related email notification for this thread.