TEXTJOIN: A Separator and a Range

About this lesson

TEXTJOIN joins everything in a range into one piece of text, with a separator between the parts. It can skip the empty cells while it does it.

By the end you can

join a whole range with one separator using TEXTJOIN.
skip empty cells while joining.

Practises TEXTJOIN CONCAT

The idea

Three arguments: the separator, TRUE or FALSE for whether to ignore blanks, and what to join. The separator is written once, however many pieces there are. That is the difference between this and a chain of ampersands. And it takes a range, so joining twenty cells is the same formula as joining three.

The second argument is the one that matters. Join three cells by hand when the middle one is empty. You get a comma with nothing after it, on every row where somebody left a field blank. TRUE removes that problem completely. FALSE is not useless. A fixed-width export needs every field to have its place, even when it is empty. But if you are building something for a person to read, TRUE is almost always what you meant.

The mistake to watch for

FALSE in the second argument when the data has gaps. Every empty cell keeps its place. So the list comes out with a comma followed by nothing, on every row where somebody left a field blank. Unless the output is a fixed-width record that needs every field present, the second argument is TRUE.

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

List the three items on order SO-118 in E2, separated by a comma and a space, using TEXTJOIN.

To begin, type it exactly:

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

Row	A	B	C	D	E
1	Order	Item 1	Item 2	Item 3	Contents
2	SO-118	hinges	brackets	screws
3	SO-119	sealant		washers
4	SO-120	dowels
5
6
7
8

Every step

1

List the three items on order SO-118 in E2, separated by a comma and a space, using TEXTJOIN. To begin, type it exactly: =TEXTJOIN(", ",TRUE,B2:D2).

Hint. The separator comes first.
2

Order SO-119 has a gap in the middle. Read =TEXTJOIN(", ",TRUE,B3:D3) and say what it returns.

Hint. TRUE means skip the empty ones.
3

Now with the opposite instruction. Read =TEXTJOIN(", ",FALSE,B3:D3) and say what it returns.

Hint. The blank still gets a separator.
4

In E4, list whatever is on order SO-120 with a comma and a space between items. Skip anything empty. Use TEXTJOIN.

Hint. Same three arguments as before.
5

Separators do not have to be punctuation. In E3, join the items on SO-119 with the word and, spaces either side, skipping blanks.

Hint. Mind the spaces inside the quotes.