The TEXTJOIN function in Excel

Q: Can TEXTJOIN join only the cells that meet a condition?

Yes, combine it with FILTER: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,C2:C20="Open")) lists every open item in one cell.

Q: How do I put each item on a new line?

Use a line break as the delimiter, CHAR(10), and turn on Wrap Text for the cell.

TEXTJOIN combines text from many cells into one, putting a separator such as a comma between each piece. Its big advantage over & or CONCAT is that it can skip empty cells, so you don't end up with doubled commas. Use it to build full addresses, email lists, or a single cell that lists every tag on a product.

Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, ...)

delimiter	The text to put between items, in quotes, like `", "` or `"; "`. Use `""` for no separator.
ignore_empty	`TRUE` skips blank cells (what you usually want). `FALSE` keeps them, which leaves empty gaps between delimiters.
text1, ...	The cells, ranges or text to join. A range like `A2:D2` counts as several items.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In E2, use TEXTJOIN to combine the three tag columns (B2:D2) into one cell, separated by a comma and a space. Skip empty tags.

	A	B	C	D	E
1	Product	Tag 1	Tag 2	Tag 3	All tags
2	Trail jacket	Waterproof		Hiking
3	Merino tee	Lightweight	Breathable
4	Camp stove	Compact	Fuel-efficient	Camping
5	Dry bag		Waterproof	Kayaking
6
7
8

Tip: The delimiter goes first in quotes. The second argument decides whether blanks are skipped.

Examples

=TEXTJOIN(", ",TRUE,A2:D2)→ "14 Mill Lane, Flat 3, York, YO1 7HH"

Joins the four address parts with a comma and space between each.

=TEXTJOIN(", ",FALSE,A3:D3)→ "220 Harbor Rd, , Portland, 97201"

This address has no unit. With FALSE, the empty cell is kept, so you'll see two commas in a row. Switch to TRUE to fix it.

=TEXTJOIN("; ",TRUE,F2:F5)→ "ana@example.com; ben@example.com; cleo@example.com"

Builds a semicolon-separated list you can paste into an email's To field. The blank row is skipped.

Example data

1	Street	Unit	City	Postcode	Team email
2	14 Mill Lane	Flat 3	York	YO1 7HH	ana@example.com
3	220 Harbor Rd		Portland	97201	ben@example.com
4
5					cleo@example.com

Common mistakes

Forgetting the space in the delimiter
"," gives York,YO1 7HH. If you want a space after each comma, include it: ", ".
Doubled delimiters
Gaps like York, , UK mean ignore_empty is FALSE. Set it to TRUE.
Dates turn into numbers
A date cell joins as its serial number, like 45678. Format it first with TEXT: TEXTJOIN(", ",TRUE,A2,TEXT(B2,"d mmm yyyy")).
#NAME? in older Excel
TEXTJOIN needs Excel 2019 or later. In older versions, use & or CONCATENATE and add the separators yourself.

FAQ

What's the difference between TEXTJOIN and CONCAT?+

CONCAT glues text together with nothing in between. TEXTJOIN adds a separator for you and can skip blank cells, which makes it better for lists.

Can TEXTJOIN join only the cells that meet a condition?+

Yes, combine it with FILTER: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,C2:C20="Open")) lists every open item in one cell.

How do I put each item on a new line?+

Use a line break as the delimiter, CHAR(10), and turn on Wrap Text for the cell.

Lesson

Master TEXTJOIN step by step: Joining text: &, CONCAT and TEXTJOIN

Build full names, addresses and tag lists by joining text with &, CONCAT and TEXTJOIN.

Related functions

FILTER TRIM LEFT