Using OLE for mail merge is a straightforward process, except for the step of formatting your data. The data that Excel receives is in its raw numeric format, unless it's marked as text in the source application. What this means is that zip codes lose their leading zeroes and nine-digit zip codes lose their hyphens, and monetary values come over as plain numbers. In order to display them in a formatted manner you must provide formatting codes in your Word document.

Formatting Codes

If you're familiar with mail merges, then you're used to seeing place holders in your document for the fields that will be filled in from your data source, such as <<first_name>>, <<zip>> or perhaps <<donation>>. These are known as "merge fields," and they vary depending on your data source, so your actual merge field names might be completely different.

Tied to each of these fields is a format that's hidden from you by default; to see the formatting info in your Word merge document, use the key combination <Alt>-F9 (i.e. hold down the "Alt" key while you press the F9 key).  When you do that you will see a different representation of your document, with curly braces and "MERGEFIELD" surrounding or preceding each merge field. Press Alt-F9 again and you'll see the layout you're used to.

When you are in the Alt-F9 MERGEFIELD layout, you can add formatting codes to define the formatting of numeric data, much the way you can use Custom Format in Excel to format numeric data. 

• Custom formats are entered after the field name, within the curly brackets.
• Number formats are preceded by "\#" (without the quotes), to indicate that a numeric format is coming.
• A "0" (zero) indicates that if there isn't a digit in that place a zero should be placed there.
• A "$" means to put a currency symbol before the number.
• A "," means to use the thousands separator when displaying the number.
• You can also embed conditional IF statements (and other Word directives) in your formats, which is useful when you are formatting mixed zip-code data.

Some helpful format commands are given below. To enter them, first use Alt-F9 to uncover the MERGEFIELD formats. Entering the special curly braces is done by using Ctrl-F9 (holding down the Control key and pressing the F9 key). "Zip" and "Amount" should be replaced with your data source's actual field names. 

If you would like a copy of this page for future reference, you can find a PDF at software.hampshire.edu/Documents%20and%20Misc%20help%20files/Mail_Merge_Field_Formatting.pdf