Format Mail Merge Fields from Excel

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. In this tutorial, learn how to format mail merge fields in Microsoft Word.

The 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.

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 and press Shift + F9, or right mouse click and select Toggle Field Codes.

The mail merge fields will then look like the image below.

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 field formatting, you may decide to format it differently. Below are some more examples of what you could do.

The following field code returns 18 November, 2014.

{ MERGEFIELD Date\@ “dd MMMM, yyyy” }

This field code includes the weekday in the returned value. It returns Tuesday, 18 November 2014

{ MERGEFIELD Date\@ “dddd, dd MMMM yyyy” }

And this field code using hyphen delimiters between the day, month and year to return 18-11-2014.

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

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” }

The following field codes returns 10:00 AM.

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

And the following field code returns 10:00:00 AM.

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

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 Price\# £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.

The following merge field returns £450.00.

{ MERGEFIELD Price\# £0.00 }

And this merge field also returns £450.00 but negative values are displayed in brackets or parenthesis. A semi-colon is used to separate the positive and negative value formats.

{ MERGEFIELD Price\# £0.00;(£0.00) }

Watch the Video