Mail merge / serial letters: Problems with the formatting of numbers, dates, times and text

We are using the ConfTool Pro export (XLS file) and Microsoft Word to create a serial letter, but the formatting of numbers, date and time fields is not as we expected. How can we adapt the formatting?

You can update the fields that are used to merge the text and define the formatting in MS Word.

In the merged document, please select the merge field to be updated and then:

• Press Shift + F9 in Windows (Use Fn + Shift + F9 in OSX) to change to "field view".
• Click with the mouse button on the field you would like to edit and add the formatting instructions (see below) at the end of the field (but before the closing curly brackets"}"). An example field would look like {MERGEFIELD session_start\@ "DD.MMMM.YYYY" } .
• Update the field by pressing F9 in Windows (Use Fn + F9 in OSX).

Alternatively you can also use ALT + F9 (Fn + Option + F9 in OSX) to toggle all fields between "field view" and "mail merge preview".


Formatting Basics
There are 3 different methods to format text.
If you are formatting text fields, start your code with \* , for numeric fields start with \# and for date-time fields start with \@.


Text Formatting
The codes for text fields (alphanumeric fields) can be found here:
Microsoft Support: Insert and format field codes in Word - Format switch
   Example Codes:
   \* Caps        This switch capitalizes the first letter of each word.
   \* FirstCap   This switch capitalizes the first letter of the first word.
   \* Upper       This switch capitalizes all letters.
   \* Lower       This switch changes all letters to lower case.


Number Formatting
The available switches are described here:
Microsoft Support: Insert and format field codes in Word - Numeric format switch
   Example Codes:
   \# "0.00" to see numbers in the format 1234.50
   \# "#,##0.00;- #,##0.00" to see positive numbers in the format 1,234.50 and negative numbers in the format -1,234.50
   \# "$#,##0.00;- $#,##0.00" to see positive numbers in the format $1,234.50 and negative numbers in the format -$1,234.50


Date and Time Formatting
The date-time field codes are explained here:
Microsoft Support: Insert and format field codes in Word - Date-Time format switch
   Example codes:
    \@ "dd-MMM-yy" to see the date in the format "28-Feb-16"
    \@ "dd/MM/yyyy" to see the date in the format "28/02/2016"
    \@ "dd MMMM yyyy" to see the date in the format "28 February 2016"
    \@ "dddd, dd MMMM yyyy" to see the date in the format "Sunday, 28 February 2016"
    \@ "HH:mm" to see the time in the format "13:45"
    \@ "HH:mm:ss" to see the time in the format "13:45:30"
    \@ "h:mm AM\PM" to see the time in the format "1:45 PM"
    \@ "h:mm:ss AM\PM" to see the time in the format "1:45:30 PM"


More information can be found at:
Microsoft Support: Insert and format field codes in Word
Microsoft Support: Field codes in Word

You can find an example file below.

Please note that you have to convert date-time fields to time fields first in Excel if you want to use the Word formatting options for times. It seems to be a bug in Word 2007 and later. To create a time field from a data field in Excel, please add an empty column and use the code:
=TIME(HOUR(C2),MINUTE(C2), SECOND(C2))
... where C2 is the original date-time field. The new fields must have a time formatting (use the right mouse button to change the formatting).
 
The code in German Excel is:
=ZEIT(STUNDE(C2);MINUTE(C2);SEKUNDE(C2))

If you want to change the formatting of the names and indexes, use the search and replace function in Word and the "use wildcard" option that allows to work with simple regular expressions.

To enable this function, press CTRL-H to open the search and replace dialogue. Then click the "More" box and select the "Use wildcards" check box (see screenshot).

You can then use (simple) regular expressions in the search field and corresponding replacement commands in the replace field to update the formatting.

Example:
The default name formatting from the export looks like:
Dillard, Alexandra L. (1); Smith, John (1); Reynolds, Jan-Hendrik (2); Oliver, Jean-Michel (1,2)
Müller, Edmund (1); Schmitt, Edward (2); Benevides, Marie (3)
However, you would prefer:
Dillard, Alexandra L.¹; Smith, John¹; Reynolds, Jan-Hendrik²; Oliver, Jean-Michel^1,2
Müller, Edmund¹; Schmitt, Edward²; Benevides, Marie³


To process the text this way, please use the following "Find what" string (it starts with one space!)

Code: [Select]

\(([0-9]*)\)and the "Replace with" string:

Code: [Select]

\1Furthermore, please use the "Format" button in the lower left corner to set the formatting for the replace text, in our case to superscript. Now you can start the replace process. Please note that you can always undo this operation by pressing CTRL-Z.

Explanation: The inside brackets (without the backslashes) determine the text that has to be updated. In this case it is ([0-9]*) and this string stands for a text that starts with a number [0-9] followed by any number of characters *. The brackets with the backslashes are the brackets of the original string, they will be removed during this operation (as they are outside the inside brackets). The backslash in front of them tells word to handle them as regular brackets and not as commands.

More details on the regular expression search options in Word are provided by Microsoft:
Find and replace text by using regular expressions (Advanced)

We also provide a VBA macro that uses this method to convert HTML code to formatted text here:
Mail merge / serial letters: How to convert HTML codes to formatted text

If the authors are shown in separate rows, you can use search and replace in Office to show them in one row.

To enable the search and replace dialogue, press CTRL-H.

To remove the newlines after the semicolons, use the following "Find what" string

Code: [Select]

; ^pand the "Replace with" string:

Code: [Select]

; (There is a space after the ; )

Explanation: The code ^p stands for a new line in the search dialogue of MS word. As usually abstracts do not end with a semicolon, this replacement should not alter the text of your abstracts.