File Format & Compatibility

Fix Excel Mail Merge Converting Mixed Text and Numbers to Zero in Word

Steve K Sep 25, 2026 874 views

Question details

The user needs to prevent Word from converting text values into zeros when performing a mail merge with an Excel column containing both numbers and text.

How to Fix Excel Mail Merge Errors with Mixed Text and Numbers

Product: Microsoft Word and Excel
Device & OS: not provided
Scenario: Executing a mail merge in Word using an Excel spreadsheet as the data source where a specific column contains mixed data types.
Observed behavior: Text entries (such as "Exempt") in an Excel column that mostly contains numbers are incorrectly converted and displayed as "0" in the merged Word document.

Before you start

Make a backup copy of your original Excel database and identify the exact column that is causing the zero-conversion issue in your Word document.

Solution 1Recommended

Add a Dummy Text Record to Force Text Detection

Because Word's data engine checks the first few rows to determine an Excel column's data type, placing a text entry in the very first data row forces Word to treat the entire column as text.

By default, the OLE DB provider used for mail merges samples the first 8 records. If the majority are numbers, it sets the data type to numeric, turning any subsequent text into a 0. A dummy row easily circumvents this.

Open your data source

Open the Excel spreadsheet that you are using as the data source for your mail merge.

Insert a new row

Right-click the row number immediately below your header row and select 'Insert' to create a blank row.

Enter a dummy text value

Type a generic text value (e.g., 'Sample Text' or 'Dummy') into the cell of the mixed-data column.

Save and refresh

Save the Excel workbook, open your Word document, and refresh the data source connection.

Skip during merge

When executing the final mail merge, simply uncheck or skip the dummy record so it does not print.

Add a Dummy Text Record to Force Text Detection

Reliable Workaround: This is the most consistent method for fixing OLE DB data type detection issues without requiring complex registry edits or connection changes.

Solution 2

Format the Excel Column as Text

Pre-formatting the mixed-data column as Text before importing it into Word can sometimes resolve data conversion inconsistencies.

Seamless Mail Merge

Perform Flawless Mail Merges with WPS Office

WPS Office offers a seamless mail merge experience between WPS Spreadsheets and WPS Writer. Its highly compatible engine accurately reads mixed data types, ensuring your text and numbers display exactly as they do in your source file without complex workarounds.

1. Open WPS Writer: Launch WPS Writer and navigate to the 'References' tab on the top ribbon.
2. Import data source: Click on 'Mail Merge' and select 'Open Data Source' to seamlessly import your WPS Spreadsheet or Excel file.
3. Insert merge fields: Place your cursor in the document and click 'Insert Merge Field' to add your mixed-data column exactly where you need it.
4. Execute the merge: Click 'Merge to New Document' or 'Merge to Printer' to generate your final files with 100% data accuracy.

Accurate interpretation of mixed text and numerical data during mail merge.Fully compatible with Microsoft Excel (.xlsx) and Word (.docx) formats.Intuitive, step-by-step Mail Merge Wizard built directly into WPS Writer.Lightweight, fast, and free alternative for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word convert my Excel text to zeros during a mail merge?

Word uses an OLE DB connection to read Excel data. This engine determines the data type of a column by analyzing the first 8 rows. If the majority of those rows contain numbers, it assumes the column is strictly numeric and converts any text values it finds into a zero.

Can I use DDE (Dynamic Data Exchange) instead of OLE DB to fix this?

Yes. Connecting via DDE forces Word to read the data exactly as it is formatted on the screen in Excel. To do this, go to Word Options > Advanced > General, check 'Confirm file format conversion on open', and select 'MS Excel Worksheets via DDE' when linking the spreadsheet.

Will formatting my Excel column as 'Text' guarantee the mail merge works?

Not always. Even if formatted as text in Excel, the OLE DB connection may still read the underlying values. If the column contains primarily numbers at the top, adding a dummy text row at the top remains the most reliable workaround.

Document & File Issues