Fix Blank Mail Merge Fields and Formula Errors in Word
Question details
Prevent Word mail merge formulas from failing when Excel source fields are blank, while treating spaces, hyphens, and numeric zero appropriately.

- 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.
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%.
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.
Open the source workbook in Excel and add a column with a unique header, such as Merge_Rating_Display.
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.
Fill the formula down the helper column and review any “Check value” results. Save the workbook; close it if Word cannot refresh the source.
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.
Choose Mailings > Preview Results and move through records with blanks, spaces, hyphens, zero, and other numeric values.

Use a Word IF field for a simple blank check
Use this approach when you only need to replace empty merge values with text and do not need to calculate a percentage.
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.

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.




