Document Editing Problems

How to Show Only Part of an Excel Field in Word Mail Merge

Steve KSteve K Oct 23, 2026 871 views

Question details

The user needs to display only a specific portion of an Excel data field (e.g., the numbers appearing before a semicolon) when running a Word mail merge.

How to Show Only Part of an Excel Field in a Word Mail Merge
Product
Word and Excel
Device & OS
not provided
Scenario
Generating merged documents or emails using an Excel dataset where a single cell contains both needed numbers and unwanted trailing codes separated by a semicolon.
Observed behavior
Word mail merge pulls the entire contents of the Excel field by default, including the semicolon and the additional codes, because Word lacks a built-in feature to split text dynamically during the merge.
Before you start

Ensure your Excel spreadsheet is saved and closed before attempting to reconnect it as a data source in Word to prevent file-locking errors.

Solution 1Recommended

Prepare the Data in Excel Using Text to Columns

Since Word cannot automatically split fields during a mail merge, the most reliable method is to separate the data into a new column within your Excel source file before merging.

Manipulating the data in the source spreadsheet is the best practice for mail merges. By creating a dedicated column containing only the values you need, you ensure Word pulls exactly the right information without requiring complex formatting.

1
Insert a Helper Column

Open your source spreadsheet in Excel. Right-click the column letter next to your target data and select 'Insert' to create a blank helper column.

2
Launch Text to Columns

Highlight the column containing the original data (numbers, semicolon, and codes). Go to the 'Data' tab on the ribbon and click 'Text to Columns'.

3
Split by Semicolon

Choose 'Delimited' and click Next. Check the box for 'Semicolon' (or type it in the 'Other' box if needed). You will see the data split in the preview pane. Click Finish.

4
Update Your Mail Merge

Save the Excel file. Open your Word document, go to the 'Mailings' tab, select your recipients again to refresh the data, and insert the newly created column field into your document.

Prepare the Data in Excel Using Text to Columns
Best Practice: Updating the spreadsheet directly prevents formatting errors in Word and saves time, especially when working with filtered subsets of large record lists.
Seamless Mail Merge with WPS Office

Manage Spreadsheet Data and Mail Merges Easily in WPS Office

WPS Office provides powerful built-in spreadsheet functions and a streamlined mail merge tool in WPS Writer, making it incredibly easy to manage large datasets, extract specific substrings, and generate perfectly formatted documents.

  1. 1. Open Data in WPS Spreadsheet: Launch WPS Office, open your data file, and use the 'Text to Columns' feature under the Data tab to isolate your target text.
  2. 2. Save Your Data Source: Save your separated data as an .xlsx file and close the spreadsheet.
  3. 3. Launch WPS Writer: Open your target document in WPS Writer and navigate to the References tab, then click on Mail Merge.
  4. 4. Connect the Data Source: Click 'Open Data Source', locate your saved spreadsheet, and insert the new, clean helper column field into your document.
  5. 5. Finish the Merge: Click 'Merge to New Document' or use the email merge options to generate your final output with perfectly formatted fields.
Effortlessly split compound data using Text to Columns in WPS SpreadsheetFully compatible with Microsoft Excel (.xlsx) and Word (.docx) formatsUser-friendly Mail Merge wizard in WPS Writer with easy data source managementLightweight, fast, and free alternative to expensive office suites
microsoft office alternative - wps office

Frequently Asked Questions

Can I use Word field code switches to split text during a mail merge?

No. While Word provides various field codes and switches (like \@ for dates or \# for numbers) to format data, it does not have built-in switches capable of string manipulation, splitting text, or hiding text after a specific character. This must be handled in the data source.

Why is my Word mail merge still showing the old Excel data after I split it?

Word often caches the data source connection. To fix this, ensure your Excel file is saved and closed. Then, in Word, go to the Mailings tab, click 'Select Recipients', choose 'Use an Existing List', and reconnect your Excel file to refresh the fields.

How do I handle Excel records that don't have a semicolon when using the LEFT formula?

If a cell lacks the delimiter character (the semicolon), the FIND function will result in a #VALUE! error. To prevent this, wrap your formula in an IFERROR function like this: =IFERROR(LEFT(A2, FIND(";", A2)-1), A2). This tells Excel to return the original text if no semicolon is found.