How to Show Only Part of an Excel Field in Word Mail Merge
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.

- 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.
Ensure your Excel spreadsheet is saved and closed before attempting to reconnect it as a data source in Word to prevent file-locking errors.
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.
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.
Highlight the column containing the original data (numbers, semicolon, and codes). Go to the 'Data' tab on the ribbon and click 'Text to Columns'.
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.
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.

Use an Excel Formula to Extract Text Dynamically
If you frequently update your spreadsheet and prefer an automated approach over the Text to Columns wizard, you can use a formula to extract the text before the semicolon.
Use Find and Replace in Word After the Merge
If you cannot modify the source spreadsheet, you can complete the merge into a new document and use Word's advanced Find and Replace to strip out the unwanted text.
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. 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. Save Your Data Source: Save your separated data as an .xlsx file and close the spreadsheet.
- 3. Launch WPS Writer: Open your target document in WPS Writer and navigate to the References tab, then click on Mail Merge.
- 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. Finish the Merge: Click 'Merge to New Document' or use the email merge options to generate your final output with perfectly formatted fields.

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.




