Change the Format of a Number Field in a Microsoft Word Mail Merge Document Using Switches

by Avantix Learning Team | Updated January 16, 2021

Applies to: Microsoft® Word® 2010, 2013, 2016, 2019 and 365 (Windows)

You can change or fix the format of a number field in a Word mail merge document using switches in field codes. Number formatting issues typically occur when the source document for the mail merge is an Excel workbook or Access database. You can use switches to change the format of a number field to display currency symbols as well as commas or decimals.

A number field is typically inserted in the main document using Insert Merge Field on the Mailings tab in the Ribbon.

Related article: How to Use Mail Merge in Microsoft Word to Generate Form Letters

Using codes in a number field switch

The common codes used in a number switch are 0 for a required digit and # for a variable number. Codes entered in switches can contain multiple parts separated by semi-colons:

# = variable number

0 (zero) = required digit

The # code will be replaced by a digit only if it is not a leading or trailing zero. You can also enter commas (,), decimals (.) and currency signs ($). Since # specifies the minimum numeric places to display, if a place has no digit, it is replaced with a space.

Number switches start with \# and the codes that follow represent the format for the number. The merge codes may appear in quotes or double quotes.

The syntax of the full mail merge number field code with the switch is:

{MERGEFIELD FieldName \# number codes} or

{MERGEFIELD FieldName \# “number codes”}

If you want to include negative formatting, add a semi-colon after the first part. This follows the same logic as Excel custom number formatting. The first part formats positive numbers, the second part (after the semi-colon) formats negative numbers. If you add another semi-colon, this creates a third part that formats zero (0) values.

Quotation marks are not required for simple number formats that do not include spaces such as {Salary \# $#,##0.00}. For more complex number formats and those that include text or spaces, enclose the number format codes in quotation marks.

Changing the format of Word mail merge number field using a switch

To use a switch to change the format of a mail merge number field in Word, you will first need to display the field code and then type the switch starting with \#:

  1. In the main Word mail merge document, select the number field.
  2. Right-click the field and choose Toggle Field Codes from the drop-down menu to display the field code. You can also press Shift + F9 to display the field code.
  3. If necessary, delete \*MERGEFORMAT from the field code.
  4. Enter the switch after the field name (such as \# $#,##0). Be sure to type a space after \#.
  5. Right-click the field and choose Toggle Field Codes from the drop-down menu to hide the field code. You can also press Shift + F9 to hide the field code.
  6. Right-click in the field and select Update Field from the drop-down menu. You can also press F9 to update the field.
  7. Run the merge by selecting Edit Individual documents from the Mailings tab in the Ribbon and then click OK in the dialog box. In the merged document, the number should appear in the switch format.

Number field formatting examples

The following are examples of mail merge number fields formatted using switches. Number switches apply rounding by default.

To change the number field formatting to a format to display a dollar sign and insert a comma as a thousands separator, we used the following switch:

{MERGEFIELD Salary \# $#,##0}

To change the number field formatting to display a comma as a thousands separator and two decimal places, we used the following switch:

{MERGEFIELD SalesAmount \# #,##0.00}

To change the number field formatting to display a number under one thousand with two decimal places and a number of one thousand or more with a comma as a thousands separator, we used the following switch (the benefit of this switch is that Word will not include a space for missing digits):

{MERGEFIELD AmountOwing \# ,#.00}

The following switch could be used with negative numbers (if the original value was 1500.57 or -1500.57, this would display $1,500.57 for a positive number and ($1,500.57) for a negative number):

{MERGEFIELD AmountOwing \# “$,#.00;($,#.00)”}

The following switch is similar to the switch above but formats zeros as a dash (the dash is considered to be text so must be enclosed in single quotes):

{MERGEFIELD AmountOwing \# “$,#.00;($,#.00);’-‘”}

Zero values can be suppressed by adding a final semicolon (;) in the switch but nothing after it. This suppresses zeros from empty fields and from fields containing zeros.

In the examples above, commas would be inserted only if the number was one thousand or more. The name of the number merge field in the above examples comes from the Excel source file. The curly brackets cannot be typed.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Related articles

How to Insert a DRAFT Watermark in Word and Remove It (Add Text or Picture Watermarks)

How to Remove Hard Returns in Word Documents

How to Superscript or Subscript in Word (with Shortcuts)

Related courses

Microsoft Word: Intermediate / Advanced

Microsoft Word: Designing Dynamic Word Documents Using Fields

Microsoft Word: Long Documents Master Class

Microsoft Word: Accessible Word Documents

Microsoft Excel: Intermediate / Advanced

Microsoft PowerPoint: Intermediate / Advanced

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some live classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom training on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

How to Show or Hide White Space in Word

How to Show or Hide White Space in Word

You can show or hide white space at the top and bottom of pages in Microsoft Word documents. If you hide white space, you won’t be able to see headers or footers in Print Layout View or the gray space that typically appears at the top and bottom of pages as you scroll through your document.

How to Create, Save, Use and Edit Templates in Microsoft Word

How to Create, Save, Use and Edit Templates in Microsoft Word

You can create, save, use and edit templates in Microsoft Word for frequently-used documents such as letters, reports, proposals and manuals. Templates include basic character, paragraph and page formatting and may include text, custom styles, tables, images, macros, sections, headers and footers.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Change Number Formats in Word Mail Merge Fields
Article Name
How to Change Number Formats in Word Mail Merge Fields
Description
You can change or fix the format of a number field in a Word mail merge document using switches in field codes. Number formatting issues typically occur when the source document for the mail merge is an Excel workbook or Access database. You can use switches to change the format of a number field to display currency symbols as well as commas or decimals.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This