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) |
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»}
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:
| 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
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}
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%}.
Kutools for Word
More than 100 Advanced Functions for Word 2003, 2007, 2010, 2013 and 2016
Comments
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 }
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
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.....
Can you do this within one merge?
Seems to do the trick on mine
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!!!!!
mohon pencerahannya
terima kasih
Eg.
Merge Data -> Merge Field
$1,500.56 -> $1,500.56
$1,500.00 -> $1,500
Has anyone answered to this query?
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! :)
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! :)
Thank you
{MERGEFIELD Card No\# "####-####-####-####"}
Note I have formatted four # keys & one - key repeating. This seems to produce a space between numbers
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!
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
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
Many thanks