Function Problems

How to Combine Text from Multiple Rows into One Cell in Excel

Maira Mehtab Sep 20, 2026 871 views

Question details

The user needs to combine text values spread across multiple Excel rows into a single cell without duplicating values.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Merging data scattered across consecutive rows into one continuous text string within a single destination cell.
Observed behavior: The text needs to be concatenated using specific formulas (such as TEXTJOIN, CONCAT, or ampersand) while handling separators and avoiding duplicate values.

Before you start

Check your current Excel version, as advanced functions like TEXTJOIN and CONCAT are supported natively in Microsoft 365 and Excel 2019 or later, while older versions will require manual ampersand concatenation.

Solution 1Recommended

Use the TEXTJOIN Function to Combine Cells with a Separator

The most efficient method for combining a range of rows into one cell, allowing you to define a specific separator and ignore empty cells.

The TEXTJOIN function is ideal when you need to merge long lists of rows and want to separate each value with a space, comma, or line break, without having to select each cell manually.

Select the target cell

Click on the empty cell where you want the combined text to be displayed.

Enter the TEXTJOIN formula

Type =TEXTJOIN(" ", TRUE, A11:A15) into the formula bar. The " " creates a space separator, TRUE tells Excel to ignore blank cells, and A11:A15 is your target range.

Apply the formula

Press Enter to execute the formula and view the cleanly combined text in your single cell.

Version Compatibility: TEXTJOIN is supported in Microsoft 365, Excel 2019, and newer. If you are using Excel 2016 or older, try the ampersand method instead.

Solution 2

Use the Ampersand (&) Operator for Manual Concatenation

Best for users on older versions of Excel or when combining a small number of specific, non-contiguous rows.

Solution 3

Use the CONCAT Function for Ranges Without Separators

A simple function used to merge a continuous range of cells into one long text string when separators are not required.

Try a smarter spreadsheet alternative

Combine Text Easily with WPS Spreadsheet

WPS Office provides powerful spreadsheet capabilities, fully supporting advanced data manipulation functions like TEXTJOIN and CONCAT to help you merge rows into a single cell effortlessly.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the rows you want to merge.
2. Select the target cell: Click on the empty cell where you want to output the combined text string.
3. Use the formula: Enter =TEXTJOIN(" ", TRUE, A1:A5) and press Enter to instantly combine the text from your chosen rows.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Natively supports advanced text functions like TEXTJOIN and CONCAT for seamless data merging.Free to download and use with a lightweight, familiar tabbed interface.Cross-platform support for Windows, Mac, Linux, iOS, and Android devices.

microsoft office alternative - wps office

Frequently Asked Questions

How do I add a line break between combined text values instead of a space?

You can use the CHAR(10) function as your separator. For example, use =TEXTJOIN(CHAR(10), TRUE, A1:A5). After applying the formula, make sure to enable 'Wrap Text' from the Home ribbon on the destination cell so the line breaks display properly.

Why is the TEXTJOIN function returning a #NAME? error?

The #NAME? error typically occurs if you are using an older version of Excel (such as Excel 2016 or 2013) that does not support the TEXTJOIN function. In this case, you will need to use the ampersand (&) manual concatenation method instead.

Can I combine text from multiple rows based on a specific condition?

Yes, you can combine the TEXTJOIN function with the IF function. For example, using the formula =TEXTJOIN(", ", TRUE, IF(B1:B5="Yes", A1:A5, "")) will only combine text from column A if the corresponding row in column B contains the word 'Yes'.

Excel & Data Problems