Function Problems

How to Combine an Excel Column into One Line with Pipe Separators

Anum MushtaqAnum Mushtaq Nov 2, 2026 869 views

Question details

The user needs to merge multiple values from an Excel column into a single text string separated by pipe (|) characters.

How to Combine an Excel Column into One Line with Pipe Separators
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.
Before you start

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.

Solution 1Recommended

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.

1
Select an output cell

Select an empty cell where you want the combined text string to appear.

2
Enter the TEXTJOIN formula

Type the formula =TEXTJOIN("|",TRUE,A2:A5), replacing A2:A5 with the actual range of your column data.

3
Generate the result

Press Enter. The cell will now display all the column values joined by pipe characters in a single line.

4
Copy to Word

Copy this resulting cell and paste it directly into your Word document.

Use the TEXTJOIN Function
Ignore Empty Cells: The 'TRUE' argument in the formula ensures that any blank cells in your selected range are ignored, preventing double pipe separators in your final text.
Efficient Data Processing

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. 1. Open WPS Office: Launch WPS Office and open your data in WPS Spreadsheet.
  2. 2. Apply TEXTJOIN Formula: Type =TEXTJOIN("|", TRUE, A1:A10) in an empty cell to merge your column.
  3. 3. Copy and Paste: Copy the result and paste it as plain text into your WPS Writer document.
Fully compatible with Microsoft Excel formulas like TEXTJOIN and CONCATFree to download and lightweightSeamless integration between Spreadsheet and WriterIntuitive user interface for fast data processing
microsoft office alternative - wps office

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.