Formatting Mail Merge Fields from Excel

2 Flares Filament.io 2 Flares ×

When performing a mail merge from Excel to Word, your mail merge fields tend to lose their Excel formatting. This is especially common with date, time and currency fields.

This tutorial will look at how you can correctly format date, time and currency mail merge fields from Excel. Once the formatting is applied to the mail merge document it will be remembered for future use.

Take the image below for instance (click to see enlarged view). Notice the formatting of the date, time and currency fields in Excel, and then the result in Word after inserting the mail merge fields.

Mail merge from Excel formatting problems

Making the Field Codes Visible

In order to correctly format the mail merge fields you need to edit the field codes. The field code is not visible by default, but it can be toggled on and off quite easily.

To make the field codes visible;

Select the mail merge field(s) you want to change.
Press Shift + F9, or right mouse click and select Toggle Field Codes.
The mail merge fields will then look like the image below.

Showing mail merge field codes for editing

Formatting the Date mail merge field

In the example used in this tutorial, the date in Excel is formatted in a UK format. But when the mail merge field is inserted it is displayed in US format.

To format the field we will need to add some information to the field code. Adding the information below will convert the field to a UK date format.

{ MERGEFIELD Date\@ “dd/MM/yyyy” }

Make sure you use uppercase M’s for the months. Lowercase m’s are used for minutes.

Now that you have control over the mail merge fields formatting, you may decide to format it differently. Below are some more examples of what you could do.

{ MERGEFIELD Date\@ “dd MMMM, yyyy” } – 18 November, 2014

{ MERGEFIELD Date\@ “dddd, dd MMMM yyyy” } – Tuesday, 18 November 2014

{ MERGEFIELD Date\@ “dd-MM-yyyy” } – 18-11-2014

Formatting the Time Mail Merge Field

When inserting the time mail merge field to the Word document, seconds were added to the time which were not on the Excel spreadsheet. To remove them, use the following field code.

{ MERGEFIELD Start_Time\@ “HH:mm” }

These field codes could also be used.

{ MERGEFIELD Start_Time\@ “HH:mm AM/PM” } – 10:00 AM

{ MERGEFIELD Start_Time \@ “HH:mm:ss AM/PM” } – 10:00:00 AM

Formatting the Currency Mail Merge Field

When inserting the mail merge field only the value is kept from the data on the spreadsheet. The accounting format from Excel has been removed.

Enter the following into the mail merge field code to restore the currency format.

{ MERGEFIELD Date\# £0 }

No decimals have been used in this example. Although once again you may decide to use another format. Here are some more you could experiment with.

{ MERGEFIELD Date\# £0.00 } – £450.00

{ MERGEFIELD Date\# £0.00;(£0.00) } – £450.00 but negative values are displayed in brackets. A semi-colon is used to separate the positive value format from the negative.

Watch the Video

Leave a Reply

Your email address will not be published. Required fields are marked *