TEXTJOIN function in Excel

TEXTJOIN joins text from several cells or ranges with a separator between the items, and can skip empty cells.

TEXTJOIN is the function to use when several cells must become one text with the same separator between them: a list of names with commas, the parts of an address, the lines of a postal label. It is available in Excel 2019, Excel 2021 and Microsoft 365.

Syntax

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

Argument	What it is
`delimiter`	The separator to put between items, such as ", " or "-". Use "" for none.
`ignore_empty`	TRUE to skip empty cells. FALSE to include them, which leaves two separators together.
`text1, text2, ...`	The cells, ranges or texts to join.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

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
Four names joined with a comma and a space between them.
=TEXTJOIN("-",TRUE,C2:E2)
Result: 78-85-91
A row of numbers joined with dashes.
=TEXTJOIN(", ",TRUE,D2:D9)
Result: 85, 52, 88, 41, 90, 61, 73
Science marks. The empty cell is skipped because the second argument is TRUE.
=TEXTJOIN(", ",FALSE,D5:D7)
Result: 41, , 90
With FALSE the empty cell keeps its place, leaving two commas together.
=TEXTJOIN(", ",TRUE,IF(B2:B9="8A",A2:A9,""))
Result: Asha, Meena, Tara, Neha
With a condition: only the names of class 8A. IF returns empty text for the others and TRUE skips them.
=TEXTJOIN(", ",TRUE,FILTER(A2:A9,C2:C9>=75))
Result: Asha, Meena, Joy, Imran
With FILTER: the students who scored 75 or more in Maths.
=TEXTJOIN(" ",TRUE,"Class",B2,"-",A2)
Result: Class 8A - Asha
Typed text and cells can be mixed.

How it works

The first argument is the separator. The second says what to do about empty cells. Everything after that is joined in order, and the separator is placed between items, never at the start or the end.

Compare it with joining by hand. For five cells, =A2&", "&B2&", "&C2&", "&D2&", "&E2 is long, and if C2 is empty it produces ", ," in the middle. =TEXTJOIN(", ",TRUE,A2:E2) is short and handles the gap.

Skipping empty cells

Set the second argument to TRUE in nearly every case. It is what makes TEXTJOIN ideal for addresses, where a second address line or a landmark is often missing:

=TEXTJOIN(", ", TRUE, B2:F2)

gives "12 Model Town, Ludhiana, Punjab" without stray commas, whichever parts are blank.

Cells that contain a formula returning "" count as empty and are skipped too. That is the trick behind the conditional example.

Joining only some rows

TEXTJOIN joins whatever it is given, so the selecting is done by another function inside it.

With IF: =TEXTJOIN(", ",TRUE,IF(B2:B9="8A",A2:A9,"")). IF returns the name for matching rows and empty text for the rest. In Excel 2019 this must be confirmed with Ctrl, Shift and Enter.
With FILTER in Excel 2021 and Microsoft 365, which reads more naturally: =TEXTJOIN(", ",TRUE,FILTER(A2:A9,B2:B9="8A")).

This answers a very common request: "list all the items for this customer in one cell".

Add UNIQUE to remove repeats and SORT to put the list in order: =TEXTJOIN(", ",TRUE,SORT(UNIQUE(C2:C13))).

Line breaks as the separator

Use CHAR(10) as the separator to put each item on its own line inside the cell: =TEXTJOIN(CHAR(10),TRUE,A2:A5). Switch on Wrap Text for the cell to see the lines.

Numbers and dates

As with all text joining, numbers and dates lose their formatting. Wrap the range in TEXT: =TEXTJOIN(", ",TRUE,TEXT(A2:A5,"dd-mmm")).

Limits

A cell can hold 32,767 characters. If the joined text would be longer, TEXTJOIN returns #VALUE!.

Common mistakes

Putting the range first. The separator is the first argument and TRUE or FALSE the second.
Leaving the second argument FALSE and getting doubled separators.
#NAME? in Excel 2016 and earlier.
Expecting to split the text again easily. TEXTJOIN is for display. Keep the original cells as your data.

In Google Sheets

TEXTJOIN is available in Google Sheets with the same three-part layout. Sheets also has JOIN, a simpler version with no option for empty cells.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

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