Combining Strings Spread in Multiple Rows

In this article, you are going to learn how to combine or merge multiple Texts, Numbers, characters, etc of different columns or rows in a cell with space or comma.

Query

We have two columns A and B. Our task is to add Both (A and B) columns text together in a cell with space.

Formula to Implement:

=CONCAT(B3:B8,C3:C8)

Join Characters of two Columns in a cell.
Join Characters of two Columns in a cell.

Download Your Example Excel File

Function applicable:

The Excel CONCAT function display text by combining multiple ranges, array and/or strings.

Syntax

=CONCAT(text1, [text2], …)

text – One or more text string to join.

Formula Explanation

=CONCAT(B3:B8,C3:C8)

Result: LEARN EXCEL

It will merge the first B3 to B8 (LEARN) then merge C3 to C8 (EXCEL) with a space in a cell F6 (LEARN EXCEL).

Remember: To Add Space=” ” or by manually put a space.

In case you want to add two columns of text with (,) comma separator. For this, we will use TEXTJOIN Function.

Query

We have two columns Color 1 and Color 2. Our task is to show each Color Name in one cell with a separator (,) comma.

Formula to Implement:

=TEXTJOIN(“, “,,B3:B6,C3:C6)

Add two Columns Text in a cell.
Add two Columns Text in a cell.

Function applicable:

The Excel TEXTJOIN function allows to concatenate (join) the text from multiple ranges and/or strings, and with a given delimiter between each text value. 

Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

delimiter – Character to specify the boundary between each text value
ignore_empty – If TRUE, ignores empty cells. The default is FALSE.
text – Strings to concatenate.

Formula Explanation

=TEXTJOIN(“, “,,B3:B6,C3:C6)
Result: Blue, Green, White, Black, Yellow, Grey

It will join first column color 1 (B3 to B6) then column color 2 (C3 to C6) with a delimiter i.e. (,) Comma and space.