Choices for formatting fields in Word Mail Merge

Office-Watch.comLast updated: 7 January 2023

Email Essentials,Microsoft 365,Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Microsoft Outlook,Microsoft Word,Office 2013,Office 2016,Office 2019,Office 2021 / Office LTSC,Office 365,Office for Mere Mortals,Office Watch

How to change the look of Word mail merge data like dates and dollar amounts to exactly what you want to see in the documents.

Nancy M asks:

“I try to merge data (such as amount, PO#, to whom, as well as the date) from an Excel database. In the Excel database the amount cells are formatted for currency with 2 decimal places and the date cells are formatted as Feb. 25, 2011 – not 2/26/11. When I merge the files, the amount drops of the ending zeros (such as 68.5 and 128). The date appears as 2/26/11. “

When Word imports mail merge data it only imports the raw data, not the formatting used in Excel. That’s because Word has it’s own formatting options.

See the merge codes (Alt + F9 – toggles the display of field codes vs results) then add formatting codes.

For example

{MERGEFIELD MoneyAmount}

becomes something like

{MERGEFIELD MoneyAmount # "$,0.00;($,0.00)"}

Dates

For date fields try something like:

{MERGEFIELD Date @ "d MMM yy"}

{MERGEFIELD Date @ "d MMM yyyy"}

{MERGEFIELD Date @ "d/M/yy"}

{MERGEFIELD Date @ "M/d/yy"}

Failing that, the brute force approach is to make extra columns in the Excel worksheet that convert the amount/date into text cells formatted the way you like – then merge those text values instead of the numeric values.

But that should not be necessary – the Word formatting codes can handle it.

The Word field formatting codes are all listed in the Office Help files for your version of Office – though the options have not changed much (or at all) for many versions of Word.

Some other useful formatting codes for mail merges:

Telephone

For US style numbers:

{MERGEFIELD Phone # "(000) 000'-'0000"}

For Australian numbers:

{MERGEFIELD Phone # "(00) 0000'-'0000"}

UK numbers are complicated with area codes between 2 to 5 digits in length. It would be best to store these numbers as text or in separate fields for area code and phone numbers.

Postal Codes

(if they are stored as numbers, not text):

US Zip codes – 5 digit

{MERGEFIELD Zip # "00000"}

US Zip codes – 9 digit

{MERGEFIELD Zip # "'00000'-'0000'"}

Australian Postcodes

{MERGEFIELD Postcode # "0000"}

UK post codes includes letters and should be stored as text.

Another and better way with Excel

Excel has much better formatting and data transformation options than Word.

For many years, we prefer to do all the formatting in Excel, with additional fields (columns) that have the dates, amounts etc as text formatted as you like.

Import your mail merge data into Excel (assuming it’s not there already). Get & Transform (aka PowerQuery) can do that very well from almost any data source known to (digital) man.

Then add columns to transform Excel fields into exactly how you want them to appear in the Word mail merge. An original field ‘Date’ can be formatted as text as a ‘Date_Formatted’ column using the Text() function. Remember that Excel shows a version of the cell, not necessarily the true value.

Use the IF() function to handle special situations like empty or unusual values.

Best of all, you can easily check the results by scrolling down the Excel columns to see the transformed text as it’ll appear in the merged documents.