Skip to main content

How to format date, currency and number during mail merge in Word?

Author: Amanda Li Last Modified: 2021-12-23

Mail merge is a useful tool to create a set of documents that are essentially the same but where each documents contain unique elements. However, when we merge some mails, we should pay more attention to the format of date, currency and number. This tutorial will show you how to format date, currency and number properly in word during mail merge.

Formatting date properly during mail merge in Word

Formatting currency and number properly during mail merge in Word

Before we change the format of date, number and currency in mail merge, we need to apply following operations firstly.

Step 1: select the merge field you want to change. In this case, it is Date field.

Step 2: Press Shift+F9 to make the field coding visible. The field you selected should now look like this:

{MERGEFIELD DATE}, {MERGEFIELD CURRENCY} or {«Percent»}


arrow blue right bubble Formatting date properly during mail merge in Word

Please add the following formatting data: \@"dd MMMM yyyy" into the merge file of date. And the merge field of date should like this:

{MERGEFIELD DATE\@ "MMMM d, yyy"}

The format of date will be like this:

change-foramt-1

Note:you can change the date format to whatever you like. Other possible formats could be: • dd/MMM/yyyy , • d/MMM/yy, • d MMMM yyyy


Easily split a Word document into multiple documents
Typically, we use copy and paste to split a Word document manually, but the Split Document utility can automatically split a document based on page, Heading 1, page break, or section break. This significantly improves work efficiency. Learn more about the Split Document feature.
ad split documents by page
Kutools for Word: Enhance your Word experience with hundreds of handy tools. Download now and see the difference!

arrow blue right bubble Formatting currency and number properly during mail merge in Word

Please add the following formatting data \# $,0.00 into the merge file of currency, and the merge field of currency should look like this:

{MERGEFIELD CURRENCY\# $,0.00}

change-foramt-2

Note:the '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:

(1). \# 0 for rounded whole numbers

(2). \# ,0 for rounded whole numbers with a thousand separator

(3). \# ,0.00 for numbers accurate to two decimal places, with a thousand separator

(4). \# $,0 for rounded whole dollar with a thousand separator

(5). \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

Besides, sometimes the number may be percentage, you may need to edit the field{ «Percent» } so that you get {=«Percent»*100 \# 0.00%}.

Best Office Productivity Tools

Kutools for Word - Elevate Your Word Experience with Over 100 Remarkable Features!

🤖 Kutools AI Assistant: Transform your writing with AI - Generate Content  /  Rewrite Text  /  Summarize Documents  /  Get Quick Answers, all within Word

📘 Document Mastery: Split Pages  /  Merge Documents  /  Export Selection in Various Formats (PDF/TXT/DOC/HTML...)  /  Batch Convert to PDF  /  Export Pages as Images  /  Print Multiple Files at once...

Contents Editing: Batch Find and Replace across Multiple Files  /  Resize All Pictures  /  Transpose Table Rows and Columns  /  Convert Table to Text...

🧹 Effortless Clean: Sweap away Extra Spaces  /  Section Breaks  /  All Headers  /  Text Boxes  /  Hyperlinks  /  For more removing tools, head to the Remove group...

Creative Inserts: Insert Thousand Separators  /  Check Boxes  /  Radio Buttons  /  QR Code  /  Barcode  /  Diagonal Line Table  /  Equation Caption  /  Image Caption  /  Table Caption  /  Multiple Pictures  /  Discover more in the Insert group...

🔍 Precision Selections: Pinpoint Specific Pages  /  Tables  /  Shapes  /  Heading Paragraphs  /  Enhance navigation with more Select features...

Star Enhancements: Navigate to Any Location  /  Auto-Insert Repetitive Text  /  Toggle Between Document Windows  /  11 Conversion Tools...

👉 Want to try these features? Download Kutools for Word now! 🚀
 
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
May i know how to set a date field {MERGEFIELD "PrintDate" } but i would like to display that date +14 days later.

earlier : MERGEFIELD PrintDate \@ "dd/MM/yyyy" then may i know how to + 14 days then display at word?
This comment was minimized by the moderator on the site
Pour le champ monétaire je fais la modification {MERGEFIELD nombre \#"# ###,00 €"}, cela fonctionne, mais quand je ferme la lettre cela n'est pas mémorisé pour l'ouverture suivante, je doit corriger à chaque ouverture de la lettre. pourquoi?
Autre souci avec le bloc d'adresse, je dois l'effacer et le réinsérer à chaque fois.
merci pour votre aide.
This comment was minimized by the moderator on the site
I used the formula above to get my fields to add the comma separator, add the dollar sign, and make negative amounts appear in parentheses, and nothing changed with the fields at all.  What could I be doing wrong?
This comment was minimized by the moderator on the site
Hi there
In MS Word, I want to use a numeric mail merge field with this switch # "$,0.##" at the end of a sentence. If I enter a full stop after the mail merge field, then if the merged data doesn't contain cents, there will be a full stop after the decimal place, which means there will be two full stops next to each other. Could you please tell me if there is a way I can specify that I only want a final full stop if the merged figure contains cents? Many thanks for any assistance
This comment was minimized by the moderator on the site
Hi, Sophie, is your data is whole number or decimal number? If all of your data is whole number, you can try this: \# $,0
This comment was minimized by the moderator on the site
HOW CAN WRITE NUMBER LIKE THIS -2.00 OR +3.00 IN MAIL MERGE
This comment was minimized by the moderator on the site
Hi, KIRAN, if you want to show the number as -$2.00 or $+3.00, you can use this field \# "+ $#,##0.00", if you want to show the number as -2.00 or +3.00, use this filed\# "+#,##0.00"
This comment was minimized by the moderator on the site
Attempting to switch a currency field into words from a mail merge field. Any ideas; nothing I'm doing is working.
MERGEFIELD "TOTALAMOUNTDUE \\* DollarText"
This comment was minimized by the moderator on the site
THANKS GUYS, U RE THE BEST!
This comment was minimized by the moderator on the site
how do i do 23rd of october 2019
This comment was minimized by the moderator on the site
hi..

my date changed to code like this; 43696
anybody can help?
This comment was minimized by the moderator on the site
Hi, I have the same problem ... In other places it is working, but it is one place where I also get the number 4043. Is it something wrong in excel or in word??? The field code looks like {MERGEFIELD Licencijos_data\@ "yyyy-MM-dd"
This comment was minimized by the moderator on the site
I also facing the same problem... i already tried all the format \@ ..... but still not working.

Im using word 2013

Help me plz
This comment was minimized by the moderator on the site
YOU ARE AMAZING.... after years of fighting with currency in mail merges, finally the easiest and RIGHT answer.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations