How to Combine an Excel Column into One Line with Pipe Separators
Question details
The user needs to merge multiple values from an Excel column into a single text string separated by pipe (|) characters.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing data in a spreadsheet to be pasted into a Word document as a single, continuous paragraph with custom delimiters.
- Observed behavior
- The goal is to transform vertical column data into a horizontal, pipe-separated string (e.g., 111|222|333|444) for easy copying to Word.
Determine which version of the spreadsheet application you are using, as older versions may not support the newer TEXTJOIN function and will require a helper-column approach.
Use the TEXTJOIN Function
The most efficient way to combine cells with a specific delimiter in newer spreadsheet versions.
The TEXTJOIN function allows you to specify a delimiter (like a pipe character) and choose whether to ignore empty cells while combining a range of text.
Select an empty cell where you want the combined text string to appear.
Type the formula =TEXTJOIN("|",TRUE,A2:A5), replacing A2:A5 with the actual range of your column data.
Press Enter. The cell will now display all the column values joined by pipe characters in a single line.
Copy this resulting cell and paste it directly into your Word document.

Use a Helper Column (For Older Versions)
A reliable workaround for older spreadsheet versions that return a #NAME? error when attempting to use TEXTJOIN.
Quickly Merge Column Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like TEXTJOIN, making it easy to manipulate and format your data exactly how you need it. You can seamlessly copy structured data from WPS Spreadsheet directly into WPS Writer without compatibility issues.
- 1. Open WPS Office: Launch WPS Office and open your data in WPS Spreadsheet.
- 2. Apply TEXTJOIN Formula: Type =TEXTJOIN("|", TRUE, A1:A10) in an empty cell to merge your column.
- 3. Copy and Paste: Copy the result and paste it as plain text into your WPS Writer document.

Frequently Asked Questions
Why am I getting a #NAME? error when using TEXTJOIN?
The #NAME? error occurs if you are using an older version of Excel (prior to Excel 2019 or Office 365) that does not support the TEXTJOIN function. In this case, use the helper-column method with the ampersand (&) operator to achieve the same result.
Can I use a different separator instead of a pipe?
Yes. In the TEXTJOIN formula =TEXTJOIN("|", TRUE, A2:A5), simply replace the pipe character "|" with your desired delimiter, such as a comma "," or a hyphen "-".
How do I remove the formulas and keep only the combined text?
Copy the cell containing the final formula result, right-click on the same cell (or a new empty cell), and select 'Paste Special' > 'Values'. This will replace the formula with the static text string, allowing you to delete the original columns safely.




