How to Format Excel Numbers in WPS Writer Mail Merge with Conditional Thousands Separators
Question details
The user needs to format Excel numeric values during a WPS Writer mail merge so thousands separators appear only when the number is large enough, while decimal values always keep two decimal places.
- Product
- WPS Office Writer and Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is merging Excel data into WPS Writer documents and wants the merged numbers to display with locale-correct conditional thousands separators and two decimal places.
- Observed behavior
- Merged numbers do not show thousands separators conditionally, or the format does not keep two decimal places, often because the numeric field code or locale separator is incorrect.
Before you change the merge field, open the Mail Merge task in WPS Writer and confirm that your WPS Spreadsheets data column uses numeric values rather than text. Also check your system regional settings to know whether your locale uses a comma or an apostrophe as the thousands separator.
Insert a Numeric Mail Merge Field Code with #,##0.00 Formatting
Use this method when merged numbers must display thousands separators only when needed and always keep two decimal places.
The field code's numeric picture switch controls how WPS Writer formats the value from WPS Spreadsheets. The thousands separator follows your regional settings, so use the correct character for your locale.
Click inside the document where the merge field should appear, then press Ctrl+F9 to insert a pair of field braces.
Type the field code as { = [MergeField] \# #,##0.00 }, replacing MergeField with your actual field name, such as { = [Amount] \# #,##0.00 }.
Right-click the field and choose Update Field, or select it and press F9, to apply the number format.
Use Mailings > Preview Results to verify that thousands separators appear only when the number is large enough and decimals show two places.
Use the Correct Thousands Separator for Your Locale
Use this solution if the field code appears correct but the thousands separators still display incorrectly in the merged document.
Format Mail Merge Numbers in WPS Writer with a Numeric Picture Switch
Use WPS Writer's field-code picture switch to apply conditional thousands separators and two decimal places to WPS Spreadsheets data during a mail merge. The field updates with F9 and respects your regional separator settings.
- 1. Open the Mail Merge task in WPS Writer: In WPS Writer, go to Mailings > Mail Merge and select the WPS Spreadsheets file that contains the numeric data.
- 2. Insert a numeric field with Ctrl+F9: Place the cursor where the number should appear, press Ctrl+F9, and type = [MergeField] inside the field braces.
- 3. Apply the #,##0.00 picture switch: Add \# #,##0.00 after the merge field reference so WPS Writer formats the value with conditional thousands separators and two decimal places.
- 4. Update the field and preview the merge: Press F9 to update the field, then click Mailings > Preview Results to make sure the formatted numbers appear as expected.
Frequently Asked Questions
Why does the thousands separator appear as an apostrophe instead of a comma in WPS Writer mail merge?
The thousands separator follows your system regional settings. If your locale uses an apostrophe as the thousands separator, use that character in the numeric picture switch and make sure you are using the correct apostrophe character.
How do I keep two decimal places even when the value is a whole number in WPS Writer mail merge?
Use the numeric picture switch \# #,##0.00 in the field code. The .00 part forces two decimal places. Update the field with F9 and preview the merge to confirm.
Can I format negative numbers or currency values in a WPS Writer mail merge field?
Yes. You can use numeric picture switches such as \# #,##0.00;(#,##0.00) for negative values, or include a currency symbol such as \# $#,##0.00. Press Ctrl+F9 to insert the field and F9 to update it.
What should I do if the merged number still shows the raw Excel value instead of the formatted value?
Check that the field braces were inserted with Ctrl+F9 rather than typed as ordinary braces, replace MergeField with the actual merge field name, and update the field with F9. Also confirm that the source column in WPS Spreadsheets contains numeric data, not text.
