How to Format Dates and Numbers in an MS Word Mail Merge

Formatting Numbers and Dates

Microsoft Word includes a powerful mail merge feature that allows you to merge data from an external source (MS Excel, MS Access, text files, etc.) to an MS Word document. You can use merges to create form letters, envelopes, labels, listings, directories, or even to create a personalized e-mail to recipients in your address list. For many merge applications, the data may include numbers and/or dates. Often, we find that the merged documents include improper formatting for these numbers or dates. For example, we may have a "Salary" field in an MS Excel worksheet that we are merging to our document, but we find that the merged field doesn't include commas or the "$" prefix symbol. Or we may have dates in our worksheet that are formatted improperly (or worse, not formatted as dates at all). This article demonstrates how you may easily control formatting for merged dates and numbers. It's always good practice to apply this technique for all dates and numbers in your merges (even if the formatting appears proper on first preview). This helps to ensure consistent formatting as appropriate for your merge application.

Things You'll Need

• Computer
• Microsoft (R) Word 2002/XP, 2003, 2007
• Microsoft (R) Excel (optional... used for data source in examples)

Instructions

1. 

   This article assumes you have a merge document in place and are familiar with MS Word merge operations. During this article, we'll illustrate using a very simple MS Word 2003 document with an MS Excel 2003 worksheet (contains data for merge).

2. 

   Open your MS Word document and insert the merge fields as appropriate for your application. By default, MS Word inserts fields without switches (so no special instructions with regard to formatting). For example purposes here, we'll look at a simple document that includes merged data from MS Excel that includes the following fields: LastName, FirstName, DOBirth, DOHire, Salary, and Soc-Sec Nbr.

3. 

   Preview the merge results to see how your numbers and dates will appear without any special formatting instructions. Even if the formatting appears acceptable on preview, you really should apply the format codes outlined below. Otherwise, you may find that some records do not format consistently. Also, note that by applying these format codes, you have COMPLETE control over how dates and numbers appear. This helps ensure your merge output appears polished and professional. To Preview in MS Word 2002/2003, click the "View Merged Data" button on the Mail Merge toolbar. To Preview in MS Word 2007, click "Preview Results" on the Mailing tab. If you see codes looking like { MERGEFIELD Name }" etc., press ALT-F9.

4. 

   There are a couple of ways to easily insert switches to your existing field codes to control formatting for number or date fields. In this step, we'll illustrate how to insert switches using the MS Word Field dialog. In step 6, we'll illustrate how to insert the codes right in the document (without using a dialog). To open the Field dialog, right click on the field, then choose "Edit Field...". Next, click "Field Codes" on this dialog.

5. 

   The Advanced Field Properties will be displayed on the right side of this dialog. The Field codes field will already include the name of the field prefixed by the code "MERGEFIELD". For example, it may say "MERGEFIELD DOBirth". Formatting switches may be appended to this value as needed. For a date field called DOBirth, we may enter something like MERGEFIELD DOBirth \@ "MMMM d, yyyy". Note that the date format switch must begin with "\@". Se step 10 for examples of other date and numbers format switches.

6. 

   In steps 4 and 5, we inserted formatting switches using the MS Word Field dialog. In this step, we'll see how to add these switches directly to your document (inside the FIELD codes) without using a dialog. To edit or view switches for a single merge field, simply right-click on the merge field and select "Toggle Field Codes". Repeat to hide the codes and return to displaying just the field name. With the field codes visible, you may type format switches to the right of the field name.

7. 

   To toggle the visibility of ALL codes in your document, press ALT-F9 (hold ALT while pressing F9). Alternatively, you may toggle visibility for all codes using the MS Word menus... in MS Word 2002/2003, select Tools, Options, View, Field Codes... in MS Word 2007, select Office button, Word Options, Advanced, Show document content, Show Field Codes. Once you can see the field code(s), you may simply click inside the right-most } mark and enter the desired switch. For example, if the field code were "{ MERGEFIELD DOBirth }", you may edit and end up with "{ MERGEFIELD DOBirth \@ "MMMM d, yyyy" }".

8. Regardless of which method you use to add the format switch to your field, remember that all number switches must begin with "\#" and all date switches must begin with "\@". Also, remember that date switches are then enclosed in quote marks (not usually necessary for number switches). In steps 9 and 10, we'll provide examples for number and date switches, respectively. If still not sure which switch to use, have a look at the Tips section where we reveal how to gather codes using MS Excel.

9. 

   Number switches must begin with "\#". Unless your format is complex (or includes text or spaces), you need not worry with enclosing the format switch in quote marks. "0" is used to force a digit, whereas "#" is used to display a digit only if available. For example, the value 23 formatted with "0000" would display "0023". The same value of 23 formatted with "#,##0" would display as "23". A period "." denotes position of the decimal point. A comma "," is used for digit grouping, as expected. To add text inside the result, simply enclose in single quote marks. For example, to format 123456789 as "123-45-6789" the format switch is "000'-'00'-'0000". Click picture at left for examples.

10. 

    Date switches must begin with "\@", and the date format portion must be enclosed in quote marks. Use "M" (must be upper case) for the month. For March, "M" = 3, "MM" = 03, "MMM" = Mar, and "MMMM" = March. Use "d" for the Day, where "d" = 4, "dd" = 04, "ddd" = Fri, and "dddd" = Friday. For the year, use "yy" or "yyyy". For the Hours, use "h" or "hh" for the hour, "m" or "mm" for the minute, and "am/pm" to include the AM/PM designation. Typical format switches for dates include "MM/dd/yyyy" or "MMM d, yyyy". To include other text inside the result, simply enclose in single quote marks. Click picture at left for examples.

11. Be aware that other switches may be appended to the switch codes (in addition to your number or date format switch). These switches always begin with "\*" and may be used to control such things as all caps (\* Upper), all lowercase (\* Lower), first letter cap (\* Caps), various number formats, etc. You may enter these manually, or for some switches you may select the switch on the Field dialog. Click "Hide Codes" if necessary, then see the options under "Field Properties".

12. 

    One very useful switch among those that are referenced in step 11 is "\* MERGEFORMAT". This switch forces MS Word to retain your formatting when the field value is updated. For example, if you apply bold or font formatting to the merge field, then the formatting will be preserved when new values are merged. You may append this switch just like the number \# and \@ switches, or simply select the "Preserve formatting during updates" option on the Field dialog box.

Tips & Warnings

• Always apply this technique for ALL dates and numbers in your merges. Otherwise, you may find that some fields do not format properly, or formatting may not be consistent. In particular with MS Excel data sources, this can be a pesky problem. By applying the techniques here, you can ensure proper formatting for all dates and numbers.

• If you are not sure which number or date format switch to use, peek at the codes used by MS Excel. When formatting cells in MS Excel (Format Cells dialog, on the Number tab), first select the value that is closest to the format you need (Number, Currency, Date, Time, etc.). Next, pick a sample that is closest to your need. Finally, click on the "Custom" category. From here, you can usually pull together a format switch that will work in MS Word. There are some differences, so not all MS Excel format strings work the same in MS Word, but many are identical or very similar. If you get stuck trying to sort out a switch, drop me a note and I'll be happy to help.

• To enter a MS Word Field code without using the toolbar or the menu, simply type the Field Code, then select the text, and press CTRL-F9. MS Word will convert the text to a Field Code and append the special {} brackets. For example, type "MERGEFIELD DOBirth", then select that text and press CTRL-F9.

Resources

• Microsoft Office - Word home