How to Fix Truncated Word Mail Merge Fields from Excel
Question details
Word mail merge is cutting off long text in certain records when pulling data from an Excel spreadsheet or a SharePoint list.
- Product
- Microsoft Word
- Device & OS
- not provided
- Scenario
- Performing a mail merge in Word using an Excel file or SharePoint list containing long text fields as the data source.
- Observed behavior
- Specific fields containing long text strings are truncated at 255 characters in the merged document, despite displaying completely in the Excel or SharePoint data source.
Ensure your Excel spreadsheet or SharePoint list is fully saved and closed before beginning, as having the source file open can sometimes lock the data connection during the merge.
Move Long Text Records to the Top of the Excel File
Reorder your data so that Word correctly identifies the column data type as long text.
When connecting to an Excel data source, Word determines the data type of each field by scanning the first eight records. If none of those initial eight records exceed 255 characters, Word limits the entire column to short text, truncating any longer entries further down the list.
Open your Excel spreadsheet or export your SharePoint list to a local Excel file.
Find the specific record or row containing the long text that is being truncated during the mail merge.
Cut and paste this row so it is positioned within the first eight rows of your dataset, ideally in the very first row right below the header.
Save and close the data source, reopen your Word document, and run the mail merge again. Word will now recognize the field as long text.
Add a Dummy First Record
Force Word to recognize the field as a long text type by inserting a temporary dummy row exceeding 255 characters.
Perform Flawless Mail Merges Using WPS Office
WPS Office offers a robust and highly compatible Mail Merge feature between WPS Writer and WPS Spreadsheets, ensuring your data is seamlessly imported without unexpected character limits or truncation issues.
- 1. Open WPS Writer: Launch WPS Writer and open your main document template.
- 2. Access the Mail Merge Tool: Navigate to the 'References' tab on the ribbon and click on 'Mail Merge', then select 'Open Data Source'.
- 3. Select Your Spreadsheet: Locate and select your Excel (.xlsx) or WPS Spreadsheets data file containing the long text records.
- 4. Insert Fields and Merge: Click 'Insert Merge Field' to place your data into the document, then click 'Merge to New Document' to generate the final output.
Frequently Asked Questions
Why is my Word mail merge cutting off text at exactly 255 characters?
Word uses an OLE DB connection to read Excel data. This connection determines the data type of a column by reading the first eight rows. If none of those rows contain text longer than 255 characters, the entire column is treated as 'Short Text', forcing truncation on any longer entries.
Can I use a SharePoint list directly for a Word mail merge?
While direct connections can sometimes be problematic and lead to formatting or truncation errors, you can bypass this by using the 'Export to Excel' feature in SharePoint and using that downloaded Excel file as your reliable mail merge data source.
Will saving my Excel file as a CSV fix the truncation issue?
Yes, often saving your Excel file as a CSV (Comma Separated Values) or a plain Text file (.txt) bypasses the OLE DB character limitation because Word reads the source directly as plain text without trying to strictly categorize column data types.
How do I stop Word from dropping leading zeros during a mail merge?
Similar to text truncation, Word tries to interpret data types automatically. To keep leading zeros (like in zip codes), open your Excel file, format the entire column as 'Text', or add a single apostrophe (') before the number to force text formatting.
