How to Format Cheque Numbers with a Dash in Word Mail Merge
Question details
The user needs to format numeric cheque numbers in a Word mail merge so that they display with leading zeros and a dash (e.g., 01-0000123) based on numeric data pulled from Excel.
- Product
- Microsoft Word
- Device & OS
- not provided
- Scenario
- Executing a mail merge from an Excel spreadsheet to a Word document where the cheque number field requires a specific layout containing dashes and leading zeros.
- Observed behavior
- Word ignores the numeric formatting switch if the underlying Excel data is stored as a text value instead of a numeric value, resulting in incorrectly formatted cheque numbers.
Verify that your cheque numbers in the source Excel file are stored as true numeric values. If they are stored as text (e.g., formatted as 'General' with typed dashes), Word's numeric formatting switches will not work.
Apply a Numeric Format Switch to the Merge Field
Use a specialized field code switch in Word to enforce leading zeros and inject a dash into the numeric output.
When importing numeric data from Excel, Word reads the raw values rather than the visual formatting applied in the spreadsheet. You can use field codes to force Word to display the numbers in a custom format.
In your Word document, press Alt + F9 on your keyboard to toggle the display from merge results to raw field codes.
Find the merge field for your cheque number. It will look something like { MERGEFIELD Cheque_Number }.
Click inside the bracket and add the numeric format switch at the end so it looks exactly like this: { MERGEFIELD Cheque_Number \# "00'-'0000000" }.
Press Alt + F9 again to hide the field codes, then right-click the field and select 'Update Field'. Click 'Preview Results' in the Mailings tab to verify the format.
Convert Text Data to Numbers in Excel
Ensure the source data in Excel is properly recognized as numeric so that the Word formatting switch can process it.
Execute Flawless Mail Merges with WPS Writer
WPS Office provides a highly capable Mail Merge feature that seamlessly supports advanced numeric formatting switches. Read data from spreadsheets and perfectly format it in your documents.
- 1. Open WPS Writer: Launch WPS Writer, open your template document, and navigate to the 'References' tab.
- 2. Connect Data Source: Click 'Mail Merge', then select 'Open Data Source' to import your spreadsheet data.
- 3. Apply Field Codes: Insert your merge fields, press Alt + F9, and add your desired custom format switches directly into the field codes.
- 4. Merge to New Document: Preview your results, then click 'Merge to New Document' to generate your perfectly formatted pages.
Frequently Asked Questions
Why does my Word mail merge drop leading zeros from Excel?
Word pulls the raw underlying value from Excel rather than the displayed format. To retain leading zeros, you must add a numeric format switch, such as \# "00000", to the merge field in Word.
Can I format text fields with a numeric switch in Word?
No. Numeric formatting switches (like \#) only apply if the source data is recognized as a number. If the data is stored as text in Excel, Word will ignore the numeric formatting switch.
How do I toggle field codes on and off in Word?
You can easily switch between viewing the underlying field codes and the merged results by pressing the Alt + F9 keyboard shortcut.
