Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Formatting Mail Merge fields

How to change the look of mail merge data like dates and dollar amounts.

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     


Nancy M asks:

"I try to merge data (such as amount, PO#, to whom, as well as the date) from an Excel 2003 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 way

After publication, Don T. kindly wrote with an additional method:

"A mail merge in Word results in the data from Excel to display as raw data.

For example, in Excel the vaule is displayed as $19,087.23. When merged to Word it is displayed as 19087.2948576. This occurs because by default, Word 2003 and later versions use OLEDB to connect to the data source when performing a mail merge. Earlier versions used DDE connections.

When you use DDE to convert the Microsoft Access or Microsoft Excel data instead of ODBC, the data is "masked" to display the correct format.

To change this setting, in Word - On the Tools menu, click Options, and the General tab. Click on the Confirm conversion at Open check box and click OK.

This will force the connection portion of the merge to prompt the user for the file and type of connection. Choose the Excel file and the DDE connection.

It is much eaiser to change the setting than to go through the Word field format process for every field in the merge document.

Beware: updates to Word or Office will return the setting to the default and you will have to set it again. "

Don's suggestion is good, thanks. 

If all the data is formatted in Excel exactly the way you want it in the mail merge then changing to DDE connections is OK. Otherwise you can leave the data transfer method as the default and handle all the formatting in Excel.

Article posted: Wednesday, 02 February 2011

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Sorting in Word
» Alphabetical order in Word
» OneNote for Windows updated
» Office for iPhone v1.2 secrecy
» Hanx Writer and Office for iPad
» Windows 8.1 update disappoints


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).