Formatting decimals ONLY for non-whole numbers in MailMerge?

Albie Aware 6 Reputation points
2022-11-04T18:46:32.85+00:00

Hello,

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.
892 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Edstein 171 Reputation points
    2022-11-07T13:13:41.127+00:00

    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
    2022-11-07T04:00:18.5+00:00

    Hi, @Albie Aware

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

    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.