Formatting Word fields with switches

Word provides the means to format the contents of fields inserted into the document, by the use of 'switches' or masks. The full range of these switches can be found at the end of this page.

The following tips are intended to cover some of the less obvious issues.

To edit the field switches, toggle between the field display and the result with ALT+F9 or use Tools > Customize to add a View Field Codes toolbar button to toggle the display. Select the item from the list and drag it to a toolbar:

The F9 key will update the field content to reflect the changes, or you could use macro code to update all the fields in the document.

Word's formatting switches can handle numbers up to 14 digits after which it rounds off the numbers. This can cause problems when serial numbers contain more than 14 digits and the last digits are rather important!

Take the example of the number 3456 7123 0987 7652, assembled as four blocks of four digits. In the data file this may be stored as a single number 3456712309877652.

Inserting the Mergefield 

will produce:

Logic, and Word's help on the subject, suggests the addition of a numeric switch thus:

will produce the required result, but in fact what happens is that the number is rounded to 14 digits thus:

The answer should be to split up the field into two parts with the aid of the SET field to assign parts of the data to bookmarks, then to display the bookmarks with the switches.

The result is:

unless there is a zero as the penultimate number in the sequence, where thanks to a bug in Microsoft's mathematics, the last number is rounded down to zero also.

It requires a little brute force to overcome this by adding the last digit back into the bookmark C. As that digit is not usually known, this means that you have to add a calculation for each of the 9 possible alternatives, so the resulting construction is never going to look elegant. But it does work.

Sometimes when merging data you may need to include additional text only when the associated field has content, or you may wish to insert a particular field without leaving a space when there is no content in that field. The simplest method is to use a conditional field or fields to place the data and associated text.

In the following example using data from an Outlook contacts list, I have prepared a simple Directory (Catalog) merge which includes various fields and associated text depending on whether the fields have content. With the three records used for the illustration, one of the conditionally included fields and text does not appear. In the first of the three illustrations, note the positions of the quote marks "" which set the limits of what is printed.

For the sake of clarification, for the second illustration I have switched on the display of formatting marks (CTRL+* or click the ¶ button on the toolbar).

In the third of the pictures is the result of the merge

Where images are included as part of a conditional (IF) field construction, the IncludePicture field does not work as anticipated. The results of the conditional field are hard coded into the merge, which does not provide the opportunity to update, following a merge to a new document. (See also Mail Merge Graphics).

Thus conventional wisdom would suggest that:

should insert the picture identified from the mergefield 'Fieldname' and nothing when the field is empty. Unfortunately this is not the case.

The following workaround demonstrates the insertion of a blank image where the condition is not met (save e.g. a 1 pixel transparent image as Blank.jpg to give the effect of nothing being inserted - or download such an image by clicking the link). Note that INCLUDEPICTURE is the primary field and the condition is achieved as part of its content rather than the other way around.

Note: You can insert the required picture into the result of the conditional field directly. and that will work also. e.g. to add an airmail image to envelopes intended for other countries, you could use the following:

Styleref fields are used to display the content of a nominated paragraph or character style - and are most frequently found in page header or footer.

If the nominated style does not exist, then the following error message is displayed:

This is not very helpful when building a template that may not have this particular style. It is, however possible to conditionally search for the error message in the field result and only insert the Styleref field when the error message is not present. You can use a wildcard in the search pattern as shown.

This produces the required content when the style is present ......

..... but nothing when it isn't

Peter Jamieson also came up with the following approach to the problem of inserting quotation marks "" in the result of a conditional mail merge.

In a recent newsgroup question the questioner wanted to insert Exhibit "A" when a certain field was empty.

The logical code for this would appear to be

but this chops off the text at the first quote mark:

You may then consider 'escaping' the quote mark with a '\' e.g.:

but unfortunately Word inserts the quotes but also the slash

One solution is to set the quote and the slash as a bookmark using a SET field and then insert the bookmarks using REF fields (the REF part is optional) thus:

An alternative approach is to use a pair of Quote fields to place the quotes characters directly e.g.

either of which produces the desired result :

Sometimes when preparing a mail merge document, you may wish to insert text based upon whether a value has been inserted in one of a number of fields.

The following example tests for whether Field1 contains "A", or Field2 contains "B", or Field3 contains "C". If any or all of those fields meet the condition, "True" is entered in the merge document. If not, "False" is entered. In a real merge situation True or False can be replaced by whatever you wish.

You could test for any content using this method, which uses a collection of conditional fields that insert a value of 1 or 0 into a calculation according to whether the value required is contained in the field. The results are added together. If the result of that calculation is 1 or more, then one of more of those fields contains the required data. Thus:

The following shows the content of the three fields and the result of the merge calculation.

The \*FirstCap switch is normally used to format fields to lower case with the first word capitalized as in a normal sentence. However with some upper case data sources, the switch doesn't appear to do anything. The solution here is to convert the field to lower case first, by using a \*Lower switch, thus:

The \*Lower switch is also useful when used in conjunction with data - e.g. user forms where the content may be entered in upper or lower case, but you wish to test for the entered content. Let's take the example of a bookmark from a user form which requires YES to be entered in the document when the content of the bookmark is 'yes' or 'YES' or 'Yes'. In this case add the \*Lower switch to the REF field and test for y followed by any other characters.

Sometimes when merging data, comprising cash amounts, especially when importing from Excel, the mergefield displays up to 14 decimal places. This is caused by the way Excel handles numeric data internally, but is easily tamed by the addition of a switch to limit the data to 2 decimal places. 

e.g. 

are produced by the following 2 fields respectively

In the second field, the switch \# "$,0.00;($,0.00)" includes a comma, which provides for the commas to indicate thousands and millions e.g. 

and an optional currency symbol - here a dollar sign. The numeric mask can also be expressed as "$,#.00;($,#.00)" where the '#' will suppress the 0 for amounts less than a dollar.

One variation I use a lot is for Cyprus Pound currency amounts:

The section after the semi colon dictates what to do with negative amounts - here they are bracketed and coloured red. The semi-colon and following section are optional.

Sometimes with decimal amounts, when the amount to the right of the decimal is zero, you may wish to show a whole number, without the decimal, whilst retaining the amount to the right of the decimal where the amount is greater than zero e.g.

$12.00, should display as $12, but $12.95 should retain its decimal amount. This cannot be achieved with a simple switch and therefore you need to insert a conditional field to display one or the other according to what comes after the decimal. In the following example I have suppressed the display of zero amounts (see section below).

Word provides special switches - *cardtext and *dollartext (detailed in the table at the end of this page) but these are somewhat limiting if you want to express amounts in words. It is, however, possible to derive amounts in words using conditional, and formula fields with the *cardtext switch.

The following example takes the decimal output from a form field, using the default form field bookmark name Text1. The fields can be easily adapted to use mergefields and any decimal currency.

Where Text1 contains the amount 1.00 - the fields produce one dollar

Where Text1 contains the whole number 2.00 (or greater) the fields produce two(or more) dollars

Where Text1 contains .01 the fields produce one cent

Where Text1 contains .50 the fields produce fifty cents

Where Text1 contains 5.75 the fields produce five dollars and seventy-five cents

etc

The method has problems with numbers over 1 million, so as a workaround, you could use something along the lines of

Frequently, percentages will display as decimals - e.g. 41% from the data may display as 0.41354836739 when merged into Word. To display the decimal as a percentage, you should create a calculated field e.g.:

You may wish to suppress the field result altogether, when the numeric content is 0 or a negative number. This can be achieved with a conditional field, or more simply with a variation on the above numeric switch e.g. \#"$,0.00;;" Any positive number will display in a dollar format (the dollar sign being optional again). Zero or negative numbers will display nothing.

The currency switches available in a calculated form field are limited, but you can suppress the zero in a calculated form field, by adding a switch to the calculation part of the field. Toggle the display (ALT+F9) to display:

Then add the switch.

Alternatively abandon the calculated form field and use instead a Word formula field thus:

Then toggle back or lock the form to display the results, which would be the same in either case.

Five digit zip codes are reported as dropping the leading zero when merging from Excel/Access data files. A simple numeric switch should fix that one

Formatting problem: the zip codes in my data source are 5-digit or 9-digit.  What I'd like to do is apply a number format that will convert all ZIP codes to ZIP + four format, i.e. 12345 becomes 12345-0000, 123450000 becomes 12345-0000 and 123456789 becomes 12345-6789

This requires the use of a conditional field which tests whether the field has five or nine digits then applies appropriate formats.

If you wish to display the 5 digit zips without the '-0000' suffix used in the above example, and show nothing when the field is empty then modify the formatting switch thus:

0r

Formatting problem:  the telephone codes in my data source are formatted as (000)000-0000 but when merged display as 0000000000. This requires a simple numeric/text format switch:

Testing for events that occur before or after a certain date

When running a mail merge, you may want to test for events that happen before or after a certain date. Mail merge does not convert dates to numbers, so if you cannot automatically derive a date number from the date in the data file, as you could in (say) Excel, a different plan is called for.

Let's say the date comes into Word in the format d/MM/yyyy or 1/10/2002 (1st October 2002) from a MERGEFIELD called Start_Date. In this example, we are looking to identify records with Start_Date entries before 1st October 2002.

The above would appear the logical check, but the check treats the date as a number and identifies that number as 1, which is the first part of the number before the slash '/'. All dates other than the first of the month will be greater than 1, so all will produce the result "False".

We therefore need to display the date in numbers that represent the date in a unique way by using the date mask yyyyMMdd which displays the date as a series of digits for year month and finally day, without any breaks. This is a number that the conditional field will view as a whole.

Thus any date before 1/10/2002 would produce "true" and any other date would produce "false"

While it would be better to ensure that the data file has separate fields for numbers and text, sometimes you have to work with what you get. In a recent newsgroup question a user wanted to test a field that contained dates or text and reproduce only those fields that contained dates. The solution relies on the fact that if you perform a calculation on a field containing text, an error message is produced e.g.:

This can be trapped in a conditional field structure by comparing the result of the field with the error message thus:

produces

if the field contains a date,

or

If the field contains text.

UK dates are often displayed using a superscripted ordinal such as 23^rd November 2002. This is simple enough to produce if the date is typed from the keyboard, but is difficult to produce automatically with fields (though there is a possible solution suggested in fellow MVP Macropod's impressive study of date fields which you can download from this site in DateCalc.zip) as shown below:-

which produces:-

Without such complexity, Word can readily manage 23rd November 2002 i.e. without the superscript, as the superscript is usually applied by Word's autoformat function, which does not affect insertions by field.

The required format can be achieved by converting the field to text and then applying the auto-formatting to the result.

For a document template start with a combination of fields:

Select both fields and save to a bookmark - call it Date

The following macro will run automatically when a new document is opened, which will convert the fields to text and autoformat to add the superscript.

Sub AutoNew()
' FormatDate Macro
Selection.GoTo What:=wdGoToBookmark, Name:="Date"
Selection.Find.ClearFormatting
With Options
    AutoFormatReplaceOrdinals = True
End With
Selection.Range.AutoFormat
Selection.EscapeKey
Selection.Fields.Unlink
Selection.EndKey Unit:=wdLine
End Sub

The macro MUST be saved in the relevant document template and not in the default template - normal.dot

While the above is ideal where the date field is pre-inserted into a document template, where you simply wish to insert a similarly formatted current date into an existing document then with a small variation the same principle can be applied. Here the date fields are saved to an autotext entry - this time save in normal.dot to enable such dates to be available to all documents. Call the autotext entry Date. The following macro inserts the autotext and then converts to text and formats the result. 

Sub InsertFormattedDate()
NormalTemplate.AutoTextEntries("Date").Insert Where:=Selection.Range
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
With Options
    AutoFormatReplaceOrdinals = True
End With
Selection.Range.Fields.Update
Selection.Range.AutoFormat
Selection.Fields.Unlink
Selection.MoveRight Unit:=wdCharacter, Count:=1
End Sub

In a database used for mail merge, you may have a field called (e.g) MINUTES that contains a time in minutes e.g. 165 minutes, that you want to display in hours and minutes. Normal date/time switches will not help here, so you need to resort to mathematics to produce the requires result

alternatively

either of which will produce

In both examples the use of the formatting switches indicated below will remove any text following the number, thus is the field MINUTES actually contains 165 minutes, the content of the field is treated as 165.

The following information extracted and edited from Word's own help files completes the picture relating to the use of formatting switches.

Format (\*) field switch
The following is a list of switches and items that they capitalize:

 

Number formats
The following is a list of number switches and their results:

Character formats and protecting previously applied formats
The following are character formatting switches and their results:
 

Numeric Picture (\#) field switch
Specifies the display of a numeric result.

For example, the switch \# $,0.00 in { = SUM(ABOVE) \# $,0.00 } displays a result such as "$4,455.70." If the result of a field is not a number, this switch has no effect.

Note: Quotation marks are not required around simple numeric pictures that do not include spaces— for example, { MarchSales \# $,0.00 }. For more complex numeric pictures and those that include text or spaces, enclose the numeric picture in quotation marks, as shown in the following picture item examples. Microsoft Word adds quotation marks to numeric picture switches if you insert a field by using the Field command (Insert menu) or the Formula command (Table menu).

Combine the following picture items to build a numeric picture switch.
 

Date-Time Picture (\@) field switch
Specifies the display of a date or time.
For example, the switch \@ "dddd, MMMM d, yyyy" in the field { DATE \@ "dddd, MMMM d, yyyy" } displays "Friday, November 24, 2002." Combine the following date and time instructions— day (d), month (M), and year (y); hours (h) and minutes (m)— to build a date-time picture. You can also include text, punctuation, and spaces.