Document Editing Problems

Fix Blank Mail Merge Fields and Formula Errors in Word

Natalie TaylorNatalie Taylor Nov 20, 2026 869 views

Question details

Prevent Word mail merge formulas from failing when Excel source fields are blank, while treating spaces, hyphens, and numeric zero appropriately.

Fix Blank Mail Merge Fields and Formula Errors in Word
Product
Microsoft Word and Microsoft Excel
Device & OS
not provided
Scenario
A Word mail merge uses an Excel field that may contain a number, an empty value, a space, or a hyphen.
Observed behavior
The merge formula can display “Unexpected End of Formula” when a source field is empty.
Before you start

Check the Excel source column for blank cells, spaces, hyphens, numeric zeros, and numbers stored as text. Confirm whether a value such as 0.75 should display as 75%.

Solution 1Recommended

Prepare a safe display field in Excel

Use a helper column to handle missing values before Word merges the data, avoiding arithmetic on an empty field.

1
Add a helper column

Open the source workbook in Excel and add a column with a unique header, such as Merge_Rating_Display.

2
Enter the display formula

In the first data row of the new column, enter =IF(OR(A2="",TRIM(A2)="-",TRIM(A2)=""),"TBD",IF(ISNUMBER(A2),TEXT(A2,"0%"),"Check value")), replacing A2 with the source rating cell. This treats blanks, spaces, and hyphens as TBD, formats numeric zero as 0%, and flags other text.

3
Fill and save

Fill the formula down the helper column and review any “Check value” results. Save the workbook; close it if Word cannot refresh the source.

4
Insert the helper field in Word

In Word, use Mailings > Select Recipients > Use an Existing List to reconnect the workbook if needed. Then choose Mailings > Insert Merge Field and select Merge_Rating_Display in place of the failing calculation.

5
Preview the records

Choose Mailings > Preview Results and move through records with blanks, spaces, hyphens, zero, and other numeric values.

Prepare a safe display field in Excel
Check the percentage scale: The Excel format 0% displays 0.75 as 75%. If the source stores 75 to mean 75%, adjust the source or formula before merging.
Free Microsoft Office alternative

Work with Mail Merge Documents and Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with documents and spreadsheet data. Its Microsoft Office format compatibility makes it easier to move existing files into WPS, although it does not repair Word's mail merge behavior.

Work with document templates and spreadsheet source data in one office suiteOpen and edit Microsoft Word DOCX and Excel XLSX filesUse a familiar interface to ease the transition from Microsoft OfficeGet free, lightweight tools for everyday document and spreadsheet editing
microsoft office alternative - wps office

Frequently Asked Questions

Why does a blank merge field cause an unexpected end of formula?

If an empty merge value is inserted where a number is expected, the resulting Word calculation may be incomplete. Prepare a safe display value in Excel or ensure the calculation receives a valid number.

How can I show TBD instead of a blank rating?

Add an Excel helper column that returns "TBD" for an empty source cell, then insert that helper column through Word's Mailings > Insert Merge Field menu.

Will the Excel helper formula treat zero as blank?

No. A numeric zero passes the number check and displays as 0% with the example formula. Check that the source cell contains a number rather than text.

How do I handle a space or hyphen in the source data?

The helper formula uses TRIM to identify spaces and a hyphen as missing values. Review other placeholders separately and standardize them in Excel if necessary.

Why does the new Excel column not appear in Word?

Save the workbook, then reconnect it in Word through Mailings > Select Recipients > Use an Existing List. Make sure the helper column has a unique header in the selected worksheet.