TEXTJOIN, CONCATENATE and CONCAT functions in Google Sheets

TEXTJOIN joins values with a separator and can skip empty cells; CONCATENATE and the & sign join values end to end.

Joining pieces of text is an everyday job: a full name from two columns, an address from five, a list of names in one cell. Sheets offers the & sign and four functions for it, and the right one depends on whether you have a separator and whether there may be gaps.

Syntax

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

=CONCATENATE(string1, [string2], ...)

=CONCAT(value1, value2)

Argument	What it is
`delimiter`	For TEXTJOIN: the separator to place between items.
`ignore_empty`	TRUE to skip empty cells.
`text1, text2, ...`	Values, cells or ranges to join.

Examples

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=TEXTJOIN(", ",TRUE,A2:A5)
Result: Asha, Ravi, Meena, Kabir
A range joined with a comma and a space.
=TEXTJOIN(", ",TRUE,D2:D9)
Result: 85, 52, 88, 41, 90, 61, 73
The empty cell is skipped.
=TEXTJOIN(", ",FALSE,D5:D7)
Result: 41, , 90
With FALSE the empty cell keeps its place.
=A2&" ("&B2&")"
Result: Asha (8A)
The & sign joins two or three pieces.
=CONCATENATE(A2," of ",B2)
Result: Asha of 8A
CONCATENATE joins a list of values.
=CONCAT(A2,B2)
Result: Asha8A
In Sheets, CONCAT joins exactly two values.
=TEXTJOIN(", ",TRUE,FILTER(A2:A9,C2:C9>=75))
Result: Asha, Meena, Joy, Imran
With FILTER: the students with 75 or more in Maths, in one cell.

Which to use

Tool	Joins	Separator	Skips blanks
`&`	two or three values	you type it	no
CONCATENATE	any number of values and ranges	none	no
CONCAT	exactly two values	none	no
JOIN	a row or column	yes	no
TEXTJOIN	values and ranges	yes	optional

For anything with a separator, TEXTJOIN is the one to learn.

TEXTJOIN

The first argument is the separator, the second says whether to skip empty cells, and the rest is what to join. The separator goes between items only, never at the ends.

With TRUE for the second argument, missing parts leave no stray separators. An address with an empty second line comes out as "12 Model Town, Ludhiana, Punjab" and not "12 Model Town, , Ludhiana, Punjab".

Joining only some rows

Put FILTER inside, as in the last example. Add UNIQUE to remove repeats and SORT to order them:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(D2:D, C2:C="North"))))

That answers the common request "list everything for this customer in one cell".

The & sign

=A2&" "&B2 is the quickest way to join two cells. Remember to add the space or comma yourself, in quotation marks. Nothing is inserted for you.

In ARRAYFORMULA, & works down a whole column: =ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B)). TEXTJOIN and CONCATENATE do not work row by row in ARRAYFORMULA. They swallow the whole range and return one long text. For a per-row join in a self-filling column, use &.

CONCAT is limited in Sheets

In Google Sheets, CONCAT takes exactly two arguments. This differs from Excel, where CONCAT joins any number of values and ranges. A formula such as =CONCAT(A2:A5) written for Excel needs CONCATENATE, JOIN or TEXTJOIN in Sheets.

Numbers and dates

Joined values lose their format. A date becomes its serial number. Wrap it in TEXT: ="Due "&TEXT(A2, "d mmm yyyy").

Line breaks

CHAR(10) is a line break. =TEXTJOIN(CHAR(10), TRUE, A2:A5) stacks the values inside one cell.

Limits

A cell can hold 50,000 characters. A join that would exceed it returns an error.

Common mistakes

The range first in TEXTJOIN. The separator is first.
Missing quotation marks around a typed separator.
Expecting CONCAT to accept a range.
Expecting TEXTJOIN to give one result per row inside ARRAYFORMULA.
A date appearing as a number in the joined text.

In Excel

TEXTJOIN and CONCATENATE are the same in Excel. CONCAT is wider there. Excel has no JOIN.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.