Formatting Issues

How to Preserve Excel Number and Text Formatting in Word Mail Merge

Chanuka Geekiyanage Sep 28, 2026 871 views

Question details

The user wants to retain specific Excel number, currency, date, and text formats when importing data into Word via Mail Merge.

How to Preserve Excel Number and Text Formatting in Word Mail Merge

Product: Word / Excel
Device & OS: not provided
Scenario: Performing a mail merge in Word using an Excel spreadsheet as the data source.
Observed behavior: Word mail merge drops the source Excel formatting (like currency symbols, trailing zeros, or capitalization), displaying unformatted raw data instead.

Before you start

Ensure your Excel data source is properly linked to your Word document and that you have successfully inserted the necessary merge fields before adjusting their formatting.

Solution 1Recommended

Use Field Code Switches to Format Mail Merge Data

Manually add formatting switches to your merge fields to control how numbers, currency, and text appear in your final document.

Because Word imports raw data rather than the visual formatting applied in Excel, you must instruct Word how to format the data using field code switches.

Toggle field codes

Right-click on the inserted merge field (for example, «Amount») in your document and select "Toggle Field Codes" from the context menu. The field will now look like { MERGEFIELD Amount }.

Add a number or currency switch

Click inside the brackets at the end of the field name and add your format switch. For a currency format with two decimals, modify it to look like this: { MERGEFIELD Amount \# "$#,##0.00" }.

Add a text capitalization switch

If you need to capitalize each word in a text field, use the Caps switch. Modify the field so it looks like this: { MERGEFIELD Somefield \* Caps }.

Update the field result

Press the F9 key on your keyboard. This hides the field codes and immediately updates the result to show your newly applied formatting.

Use Field Code Switches to Format Mail Merge Data

Date Formatting: You can also format dates using a switch like \@ "MMMM d, yyyy" to display dates as 'January 1, 2024'.

WPS Writer Mail Merge

Perform Mail Merge Effortlessly with WPS Office

WPS Office provides a fully featured Mail Merge tool within WPS Writer that seamlessly connects with WPS Spreadsheet data, allowing you to easily manage field codes and perfectly format your documents.

1. Open your document in WPS Writer: Launch WPS Writer and open the document you wish to use as your mail merge template.
2. Start Mail Merge: Go to the References tab and click on Mail Merge to open the Mail Merge toolbar. Connect your WPS Spreadsheet data source.
3. Insert and Format Fields: Insert your merge fields, then right-click to toggle field codes and apply formatting switches just like in Microsoft Word.
4. Preview and Finish: Click View Merged Data to check your formatting, then proceed to merge the data to a new document or directly to a printer.

Seamlessly connects WPS Spreadsheets with WPS Writer for accurate mail mergesHighly compatible with Microsoft Office Word and Excel file formatsEasy-to-use toggle field codes for custom number, date, and text formattingFree, lightweight, and fast office suite

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word ignore my Excel formatting during mail merge?

Word pulls the raw data from the Excel cells via OLE DB, ignoring the surface-level display formatting (like currency symbols or decimal places) applied in Excel.

How do I format a mail merge field to show percentages?

Right-click the field, choose Toggle Field Codes, and add a percentage switch, such as \# "0.00%", to the end of the merge field before updating it with F9.

Can I keep custom date formatting from Excel?

Yes, you can replicate it in Word by adding a date switch. Toggle the field code and add \@ "dd/MM/yyyy" or your preferred date format layout to the merge field.

What if pressing F9 doesn't update the field code?

If F9 doesn't work, you can right-click the field and select "Update Field" from the context menu to apply the new formatting switch and view the result.

Document & File Issues