When you run a mail merge in Word, numbers from your data source often appear with too many decimal places, missing commas, or incorrect currency symbols. This happens because Word treats numeric data as plain text by default. This article explains how to apply number formatting directly inside mail merge fields using field codes so that prices, percentages, dates, and other numeric values display exactly as you need them.
Key Takeaways: Formatting Numbers in Word Mail Merge Fields
- Alt+F9 to toggle field codes: Reveals the underlying code of every merge field so you can add formatting switches.
- \# “#,##0.00” switch: Adds commas and controls decimal places for general numeric fields.
- \# “$#,##0.00” switch: Formats numbers as currency with a dollar sign and two decimals.
- \@ “MMMM d, yyyy” switch: Formats date fields to show the full month name, day, and four-digit year.
Understanding Mail Merge Field Codes and Formatting Switches
A mail merge field in Word looks like «MERGEFIELD Amount». Behind that visible placeholder is a field code that Word uses to pull data from your source file — typically an Excel spreadsheet, Access database, or Outlook contacts. By default, Word inserts the raw value exactly as it appears in the data source. If Excel stores 1234.5 as a number, Word shows 1234.5. If the source contains 0.1, Word shows 0.1, not 10%.
To control the display, you add a numeric picture switch (\#) or a date-time picture switch (\@) inside the field code. These switches tell Word how to format the number or date before showing it in the merged document. The switch is placed after the field name, inside the curly braces that define the field.
Prerequisites: You need a mail merge document already set up with at least one merge field. The data source must contain numeric or date values. You do not need any add-ins or special permissions. All formatting is done using built-in Word field codes.
Steps to Format Numbers in Mail Merge Fields
- Open your mail merge main document
Open the Word document that contains your mail merge fields. This can be a letter, label, envelope, or any document type. - Press Alt+F9 to show field codes
This keyboard shortcut toggles the display of all merge fields from their visible placeholders (like «MERGEFIELD Amount») to their underlying field codes (like { MERGEFIELD Amount }). You will see curly braces around each field. - Locate the numeric merge field you want to format
Find the field code that contains the number you need to format. For example, { MERGEFIELD Price } or { MERGEFIELD Percentage }. - Add the \# switch with your desired format
Click immediately after the field name but before the closing curly brace. Type a space, then \# followed by the format pattern in double quotes. For a price with two decimals and a dollar sign, type:\# "$#,##0.00". The full field code becomes:{ MERGEFIELD Price \# "$#,##0.00" }. - Press Alt+F9 again to return to normal view
This hides the field codes and shows the formatted placeholder. You will see something like $1,234.50 instead of the raw number. - Preview the results
Click Mailings > Preview Results to see how the numbers look with actual data from your source. If the format is wrong, press Alt+F9 again and adjust the pattern. - Complete the merge
Once the formatting looks correct, click Finish & Merge and choose your output method — Print Documents, Send Email Messages, or Edit Individual Documents.
Common Numeric Format Patterns
The table below shows frequently used \# patterns. Replace the pattern inside the double quotes with the one you need.
| Desired Output | Switch Pattern | Example Input | Example Output |
|---|---|---|---|
| Whole number | \# “#,##0” | 12345.67 | 12,346 |
| Two decimal places | \# “#,##0.00” | 12345.6 | 12,345.60 |
| Currency with dollar sign | \# “$#,##0.00” | 49.9 | $49.90 |
| Percentage with two decimals | \# “0.00%” | 0.085 | 8.50% |
| Leading zeros, 5 digits | \# “00000” | 42 | 00042 |
| Negative numbers in parentheses | \# “#,##0.00;(#,##0.00)” | -500 | (500.00) |
Formatting Dates in Mail Merge Fields
Date fields use the \@ switch instead of \#. The pattern follows the same rules as custom date formats in Excel. Add the switch after the merge field name just like you do for numbers.
- Press Alt+F9 to show field codes
Locate the date field, for example { MERGEFIELD OrderDate }. - Add the \@ switch with a date pattern
Type a space, then \@ followed by the pattern in double quotes. For a full date like January 15, 2025, use:\@ "MMMM d, yyyy". The full field code becomes:{ MERGEFIELD OrderDate \@ "MMMM d, yyyy" }. - Press Alt+F9 and preview
Return to normal view and check the result. Adjust the pattern if the date appears incorrectly.
Common Date Format Patterns
| Desired Output | Switch Pattern | Example Output |
|---|---|---|
| 01/15/2025 | \@ “MM/dd/yyyy” | 01/15/2025 |
| January 15, 2025 | \@ “MMMM d, yyyy” | January 15, 2025 |
| 15-Jan-2025 | \@ “dd-MMM-yyyy” | 15-Jan-2025 |
| 2025-01-15 | \@ “yyyy-MM-dd” | 2025-01-15 |
| Wed, Jan 15 | \@ “ddd, MMM d” | Wed, Jan 15 |
Common Mistakes and Things to Avoid
Numbers still show too many decimals after adding the switch
The most common cause is placing the switch outside the field code curly braces. The entire switch must be inside the braces. Press Alt+F9 and verify that the \# pattern appears before the closing brace. Also check that you used a space after the field name and before the backslash.
Currency symbols appear as plain text instead of formatted
Word does not automatically apply currency formatting from Excel. You must add the currency symbol inside the pattern, for example \# “$#,##0.00”. If your data source uses a different currency, replace the dollar sign with the appropriate symbol — but note that Word may not render non-ASCII currency symbols correctly in all fonts.
Percentage values show as 0.085 instead of 8.5%
If your data source stores 8.5% as 0.085, use the pattern \# “0.00%” which multiplies the value by 100 and appends the percent sign. Do not pre-multiply the data in Excel because Word will double the multiplication.
Dates show as serial numbers (like 45678)
This happens when the data source stores dates as text. Word cannot format a text string with a date picture switch. Convert the text to a real date in Excel before merging. In Excel, use the DATEVALUE function or change the cell format to Date.
Formatting disappears after updating the data source
When you reconnect or refresh the data source, Word may reset the field codes. Always reapply the \# or \@ switch after reconnecting. To avoid this, keep a backup of the main document with the switches already added.
Mail Merge Field Code vs Format Painter: Number Formatting Differences
| Item | Field Code Switch (\#) | Format Painter / Direct Formatting |
|---|---|---|
| Where formatting is applied | Inside the field code | To the placeholder text in the document |
| Persistence after merge | Survives merge; output uses the switch | Lost after merge; output shows raw value |
| Decimal control | Precise control via pattern | No control; shows source precision |
| Currency symbol insertion | Automatic when included in pattern | Must be typed manually |
| Date formatting | Uses \@ switch | Not applicable |
Format Painter or direct font formatting changes only the appearance of the placeholder text on screen. When the merge runs, Word replaces the placeholder with the raw value from the data source, discarding any visual formatting. The \# and \@ switches are the only reliable methods to control number and date formatting in the final merged output.
Now you can format numbers, currencies, percentages, and dates inside mail merge fields using the \# and \@ switches. Start by pressing Alt+F9 to view field codes, then add the appropriate pattern. Test your formatting with Preview Results before completing the merge. For complex formats like negative numbers in parentheses, use the two-part pattern syntax shown in the table above.