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

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


Extra Spaces (help you quickly remove all extra space from the whole document or a selection)

doc remove extract space
Kutools for Word: Add 100 New Advanced Features to Word 2003/2007/2010/2013/2016.
Office Tab: Enable Tabbed Editing and Browsing in Office, Just Like Chrome, Firefox, IE 8/9/10.
Classic Menu for Office: Bring Old Look of Office 2003 Back to Office 2007, 2010, 2013 and 2016.

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

Hot
Amazing! Using Tabs in Word like Firefox, Chrome, Internet Explore 10!

Learn more Free download

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

Kutools for Word with multiple handy Word add-ins, free to try with no limitation in 45 days.Get it Now.

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


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

arrow blue right bubble Kutools for Word

More than 100 Advanced Functions for Word 2003, 2007, 2010, 2013 and 2016

shot-kutools-700-225-kte

More Features  |  Free Download  |  Only $39.00 for hundreds of functions

Comments  

Permalink 0 Jimbobaleeno
Fantastic!. Thanks. I notice also that you can change the format in word to any of the available options in excel. To see the options go to the Excel home tab > format drop down box > more number format options > custom.
2014-06-30 02:02 Reply Reply with quote Quote
Permalink -1 Martin
Formatting the date in word 2013 does not work using \@"dd mmm YYYY" you just get an error to state the field doesn't exist. Microsoft state to use DDE but all that does is give the OOPS SOMETHING WENT WRONG message to appear on opening for the second time.
2014-08-17 09:16 Reply Reply with quote Quote
Permalink +2 Matthew
Quoting Martin:
Formatting the date in word 2013 does not work using \@"dd mmm YYYY" you just get an error to state the field doesn't exist. Microsoft state to use DDE but all that does is give the OOPS SOMETHING WENT WRONG message to appear on opening for the second time.


Make sure you either right-click on the merge field and choose 'Toggle Field Codes' or press Shift-F9 with the merge field selected.

So, rather than «LastPaidDate» you should be seeing { MERGEFILD \@ "ddMMMyy" LastPaidDate }
2014-09-09 02:21 Reply Reply with quote Quote
Permalink -1 Martin
Word 2013 date format on merge from xlsx. Undid Microsoft "fix" switched back to OLE from DDE and your fix worked 2nd time around. Brilliant. Thanks
2014-08-17 09:25 Reply Reply with quote Quote
Permalink +1 dbhope
How do you do this Martin? I'm pulling my hair out.
2014-09-05 18:13 Reply Reply with quote Quote
Permalink +5 Martin
Quoting dbhope:
How do you do this Martin? I'm pulling my hair out.

Not sure why Microsoft have made this so complicated.

I think you are just not following the instructions precisely.
1) Set up an xlsx file with two headings DATE and DATETEXT
Set format for DATE column to dd/mm/yyyy
Set format of DATETEXT to text

2) enter the a these 2 dates in the DATE column:
01/06/2013
05/07/2014

3) enter this text in the DATETEXT column & don't let it format into a date:
1st Jun 2013
5th Jul 2014

4) Save & close file
5) Open a new word doc
6) Click mailings, Start mail merge & letters, Select Recipients & use existing list & select the list you just made & click OK

7) Click Insert merge field & click DATE & then OK
8) Press enter to move to next line
9) Click Insert merge field & click DATETEXT & then OK

10) Click Preview Results & jump through the 2 entries. The DATE field will be showing the date as mm/dd/yyyy REALLY ANNOYING

11) Click once on the DATE field (to highlight it)
12) Press SHIFT & F9 and you will see:
{ MERGEFIELD DATE }
enter this text \@"dd MMM yyyy" so that the field becomes:
{MEGEFIELD \@dd MMM yyyy" DATE}

13) VERY IMPORTANT: Note the lower case dd, UPPERCASE MMM, lower case yyyy

14) Click preview results until the data shows. Scroll through entries & I hope we are sorted.....
2014-09-10 20:30 Reply Reply with quote Quote
Permalink +3 Linda
This works for me but ONLY if I close the Excel source file before running the merge.
2014-11-24 12:09 Reply Reply with quote Quote
Permalink +2 martin
I can only alter the xls merge file contents when other file closed but seems to be formatted Ok with both open. Maybe the first time you need to close the xls file to get formatting to set right when setting up files. If it works go with it.
2014-11-27 14:10 Reply Reply with quote Quote
Permalink +1 Sue
I have a list where monetory amounts are to be paid in different currencies - I have done the field toggle for GBP but of course it changes each letter to that but I need some to show USD/SGD etc
Can you do this within one merge?
2014-11-27 14:38 Reply Reply with quote Quote
Permalink +1 martin
It does sound unlikely without using visual basic to detect the denomination to rejig the merge field. I think depending on the complexity of the report I would be looking to produce it direct from excel. That may be a total non-starter.
2014-12-01 18:17 Reply Reply with quote Quote
Permalink +1 Ronel
Martin your comments really worked, I am using the "d MMMM yyyy" format and it works perfectly. Is there a way to format this to upper case?
2015-01-27 11:05 Reply Reply with quote Quote
Permalink +1 Ronel
I am using the 'd MMMM yyyy' format. Is there a way to format this to upper case?
2015-01-27 11:08 Reply Reply with quote Quote
Permalink +1 Martin
Ronel, When date is displaying right click the date. Select FONT from dialogue. Tick All Caps & then click OK.
Seems to do the trick on mine
2015-02-01 19:20 Reply Reply with quote Quote
Permalink +1 Martin
Ronel: Used to be done through the use of *Upper within field codes display. Seems to have been dropped by MS. Try this instead:
When date is displaying as 2-December-2015 or whatever your format is, right click on the date & select FONT. The dialogue box has and ALL CAPS tick box to bottom right. Click it & OK dialogue. Seems to do the trick but the coding is not displayed with Alt+F9 it must be in the background somewhere. Jeez I wish they would just leave it alone!!!!!
2015-02-01 19:17 Reply Reply with quote Quote
Permalink +1 Jessie
How about when the field is a number and letter? example "1a". It appears as "0" on the merge doc.
2015-02-20 16:19 Reply Reply with quote Quote
Permalink 0 Martin
No it doesn't. It might if you try to format it as a date but I am not sure why you would want to do that as it is not a date - well not on earth :D . As 1a is not a date if you want to display 1a just format as text as per Mr. or Mrs. or a client name.
2015-02-28 09:10 Reply Reply with quote Quote
Permalink +1 Jessie
what about a field with a number and a letter? example "1a". it displays as "0".
2015-02-20 16:22 Reply Reply with quote Quote
Permalink 0 Jo
Can someone help me with the 'day before' from another merge field?
2015-05-04 05:52 Reply Reply with quote Quote
Permalink 0 kemod
di office 2010 menggunakan MS Excel Worksheet via DDE (*.xls) tidak semua list kolom di excel terbaca karena di excel sampai Kolom LI sedangkan di wor yang terbaca sampai kolom BN
mohon pencerahannya
terima kasih
2015-05-30 09:20 Reply Reply with quote Quote
Permalink 0 Sandra
Thank you very much! :lol:
2015-07-30 18:24 Reply Reply with quote Quote
Permalink 0 Serena
Is there anyway to edit the merge field so that whole numbers do not display decimals .00 but will still include the decimals if there is cents in the merge data? We use merging to prepare thank you letters for donations and my boss doesn't think .00 looks appropriate, especially when the donation is large - $150,000.00 looks funny compared to just putting $150,000.

Eg.
Merge Data -> Merge Field
$1,500.56 -> $1,500.56
$1,500.00 -> $1,500
2015-09-11 17:11 Reply Reply with quote Quote
Permalink 0 MERI
Serena, We use { IF { MERGEFIELD Gf_Amount } = "*.00" "{ MERGEFIELD Gf_Amount \#$,# }" "{ MERGEFIELD Gf_Amount }" } where Gf_Amount is the name of the data field we're merging in.
2016-04-08 15:25 Reply Reply with quote Quote
Permalink 0 HEIN
Hi Serena try this one {MERGEFIELD "Gf_Amount" \$ "R 0 000"}, it also do not display cents although they r there
2016-05-16 11:58 Reply Reply with quote Quote
Permalink 0 HEIN
I have a question, i have a date field {MERGEFIELD "Term_Date_sys" }, but i would like to display that date + 4 months, is it possible?
2016-05-16 12:03 Reply Reply with quote Quote
Permalink 0 Shahn
Quoting Serena:
Is there anyway to edit the merge field so that whole numbers do not display decimals .00 but will still include the decimals if there is cents in the merge data? We use merging to prepare thank you letters for donations and my boss doesn't think .00 looks appropriate, especially when the donation is large - $150,000.00 looks funny compared to just putting $150,000.

Eg.
Merge Data -> Merge Field
$1,500.56 -> $1,500.56
$1,500.00 -> $1,500

Has anyone answered to this query?
2016-01-07 12:34 Reply Reply with quote Quote
Permalink 0 Serena
Hi Shahn, no I haven't received any solutions so far.
2016-02-05 16:05 Reply Reply with quote Quote
Permalink 0 ELAINE
Hello,
I was facing the same problem and manage to solve it by following the instructions here.
https://support.office.com/en-us/article/Format-mail-merge-numbers-dates-and-other-values-in-Excel-990e6516-49bc-4a43-aecc-acd027d92a00

However, after selecting "MS Excel Worksheets via DDE (*.xls)", click_FilterDat abase instead of Entire Spreadsheet.

That will solve the problem.

I hope it helps! :)
2016-06-09 03:19 Reply Reply with quote Quote
Permalink 0 ELAINE
Hi Shahn,

You can follow the instructions given here to resolve your problem.
https://support.office.com/en-us/article/Format-mail-merge-numbers-dates-and-other-values-in-Excel-990e6516-49bc-4a43-aecc-acd027d92a00#feedbackText
However, after selecting MS Excel Worksheets via DDE (*.xls), I click "_FilterDatabas e" instead of "EntireSpreadsheet".
Hope this helps! :)
2016-06-09 03:21 Reply Reply with quote Quote
Permalink 0 Hilary Schaub
I'm having a very hard time getting a simple currency to export from Excel to Word Mail Merge. I'm getting 13.5 when it sHould be $13.50
2016-02-08 18:05 Reply Reply with quote Quote
Permalink 0 Tanya
I am creating a Mail Merge Document from my loan manager database and I have trying to format the date to be September 10, 2014 instead of 09/10/14. I have tried MERGEFIELD \@ "MMMM d, yyy" CloseDate but continually receives an error " Error! Unknown character in picture string" when I toggle field codes.
2016-08-03 16:46 Reply Reply with quote Quote
Permalink +1 Karen
Try MERGEFIELD \@ "MMMM d, yyyy" - I had the message when I used 3 "y"s, but when I changed it to 4, all worked.
2016-08-24 18:43 Reply Reply with quote Quote
Permalink 0 Tanya
I am attempting to format the Merge Field to display the Close Date as September 10, 2014 instead of 09/10/14. I have tried to enter the field codes as MERGEFIELD \@ "MMMM dd, yyy" CloseDate and I even tried to Toggle Field Codes. Can anyone offer any suggestions on this? I have tried a few of the suggestions posted here but none have helped in my case.
2016-08-03 17:11 Reply Reply with quote Quote
Permalink 0 Lanie
I'm trying to make a merge field for credit card number with a format like this 0000-0000-0000- 0000 anybody know how.
Thank you
2016-08-30 06:24 Reply Reply with quote Quote
Permalink 0 Martin
Lanie. Set up an excel file with Card No as a heading. Put a 16 digit card number in (no spaces) & merge it to word. Shift F9 to display field & add the \ onward text below

{MERGEFIELD Card No\# "####-####-####-####"}

Note I have formatted four # keys & one - key repeating. This seems to produce a space between numbers
2016-09-10 13:29 Reply Reply with quote Quote
Permalink 0 Martin
Lanie - I can't work out how to get the - to appear!!
2016-09-10 13:33 Reply Reply with quote Quote
Permalink 0 Phil
Thanks for all the tips!
I found (in Word 2016) that toggling the field code and manually inserting the switch code didn't really work. I was trying to get the date as dd/MM/yyyy, but Word gave me something else entirely (no idea why the date 10/10/2016 displayed as: 10/00/2016).
Anyway, I figured out how to get Word to produce the date formatted correctly: Right-click on the merge field and select "Edit field...". Then in the window I clicked on "Field Codes" in the bottom left, and pasted the switch code in the end of the text box.
Finally it worked!
2016-10-05 01:32 Reply Reply with quote Quote
Permalink 0 Waseem
Dears,
I have little problem in my mail merge... The display date I need it to be shown in another language like arabic. For example, the date today is "October 30, 2016", when i switch or hit the toggle field code (Alt+F9) it will shown as
{ MERGEFIELD mydate } --> 10/30/2016
When i want to reformate it i will be like the following
{ MERGEFIELD mydate \@ "ddmmyy"} --> 30102016
But when i want it with what i want it to be :
{ MERGEFIELD mydate \* Arabic} --> 0

How can i make the display date look like
١٠/٣٠/٢٠١٦

Thanks and regards
2016-10-30 06:59 Reply Reply with quote Quote
Permalink 0 Robin
I am wondering how you would format if the field has a zero and you want it to be blank anything greater than zero you want the dollar amount. Thanks.
2016-12-29 21:09 Reply Reply with quote Quote
Permalink 0 Michelle
I am having issues with a mail merge from excel. I need the data to be pulled to come over as a whole number however when it comes over it is is a 0 or the first number of the number represented in excel.

Examples
What is is Excel
31%
112%
15%

What comes over in the merge field
0
1
1

How do I get the full number to come over? When I change the toggle items to \#0 it then comes over as
.31
1.12
1.5

Can you assist?

MB
2017-02-03 15:22 Reply Reply with quote Quote

Add comment


Security code
Refresh