Formatting Issues

How to Keep Excel Number Formatting in Word Mail Merge

Bushra Parveen Sep 28, 2026 868 views

Question details

The user needs to retain Excel's displayed number formatting, such as thousands separators and decimal places, when importing the data into a Word mail merge.

How to Keep Excel Number Formatting in Word Mail Merge

Product: Excel
Device & OS: not provided
Scenario: Executing a mail merge from an Excel dataset into a Word document where precise numeric presentation is required.
Observed behavior: The Word mail merge displays the underlying raw, unformatted numeric values from Excel instead of the formatted numbers shown in the spreadsheet cells.

Before you start

Before starting your mail merge, finalize your Excel dataset and identify which specific columns containing numbers, currencies, or dates need to retain their exact visual formatting.

Solution 1Recommended

Use the TEXT Function to Convert Numbers to Formatted Text

The most reliable way to preserve number formatting for a mail merge is to use Excel's TEXT function, which converts the raw number into a text string with your exact desired format.

Because Word pulls the raw background data rather than the visual formatting applied in Excel, you must explicitly convert the numeric value into a text string in a new helper column before performing the merge.

Insert a helper column

Right-click the column header next to your target number column and select 'Insert' to create a new blank column.

Apply the TEXT formula

In the first data cell of the new column, type the formula =TEXT(C2, "#,000.00") (replace C2 with the cell containing your raw number). This specific format code applies a thousands separator and two decimal places.

Fill the formula down

Click and drag the fill handle at the bottom right corner of the cell down to apply the formula to all rows in your dataset.

Update your Mail Merge

Save your Excel file. In your Word document, replace the original number merge field with the new text column you just created.

Use the TEXT Function to Convert Numbers to Formatted Text

Format Codes: You can adjust the format code within the quotation marks to match your specific needs, such as "$#,##0.00" for standard currency.

Solution 2

Append Decimals Using the Concatenation Operator

If you only need to ensure two decimal places are displayed and do not require thousands separators, you can use a simple concatenation formula.

Advanced Data Formatting

Seamlessly Manage Mail Merges with WPS Office

WPS Office provides a highly compatible and user-friendly environment for handling data and documents. You can easily use the TEXT function in WPS Spreadsheet to format your data, and seamlessly merge it into WPS Writer without losing any formatting.

1. Prepare your data in WPS Spreadsheet: Open your dataset in WPS Spreadsheet and insert a new column next to the numbers you want to format.
2. Apply formatting as text: Use the TEXT function, for example =TEXT(A1, "#,##0.00"), to format your numbers as text strings.
3. Initiate Mail Merge in WPS Writer: Save your spreadsheet, open your document in WPS Writer, and navigate to the 'References' tab.
4. Connect and insert fields: Click 'Mail Merge', open your formatted WPS Spreadsheet data source, and insert the newly formatted text fields into your document.

Fully compatible with Microsoft Excel (.xlsx) and Word (.docx) formats.Intuitive Mail Merge wizard in WPS Writer connects perfectly with WPS Spreadsheet.Supports advanced Excel functions including the TEXT function for precise data formatting.Free, lightweight, and easy to use across Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word drop the formatting of my Excel numbers during a mail merge?

Word pulls the underlying raw data from the Excel cells rather than the visual formatting applied. To keep the formatting, the data must be converted to text in Excel, or formatting must be applied directly in Word using field switches.

Can I format the number directly in Word instead of Excel?

Yes, you can edit the merge field directly in Word. Press Alt+F9 to reveal field codes, then add a numeric picture switch like \# "#,##0.00" to the end of the merge field before updating it.

How do I preserve date formatting in a mail merge?

Similar to numbers, you can use the TEXT function in Excel for dates. Use a formula like =TEXT(A2, "mm/dd/yyyy") to ensure dates appear exactly as desired when merged into your Word document.

Will changing the cell format to 'Text' in Excel fix the issue?

Simply changing the cell formatting dropdown to 'Text' usually does not work for existing numbers because the underlying raw value remains unchanged. You must use the TEXT function to actively convert the values into text strings.

Document & File Issues