Lessons Lesson 62

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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

TEXTJOIN takes a separator, TRUE to skip blanks, then the range.

In E2, type:

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

Remember: the separator is text, so it goes in quotes.

E2
Row A B C D E F G
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. 1

    TEXTJOIN takes a separator, TRUE to skip blanks, then the range. In E2, type: =TEXTJOIN(", ",TRUE,B2:D2). Remember: the separator is text, so it goes in quotes.

    Hint. The separator comes first, then TRUE, then the range.

  2. 2

    C3 is empty. Read =TEXTJOIN(", ",TRUE,B3:D3) and say what it returns.

    Hint. TRUE means skip the empty ones.

  3. 3

    FALSE keeps the empty cells. Read =TEXTJOIN(", ",FALSE,B3:D3) and say what it returns.

    Hint. The blank still gets a separator.

  4. 4

    In E4, list B4:D4 with TEXTJOIN, skipping the empty cells. Shape: =TEXTJOIN(text, TRUE, range). Use a comma and a space between items.

    Hint. Same three arguments as before.

  5. 5

    Separators do not have to be punctuation. In E3, join B3:D3 with " and " as the separator, skipping blanks. Remember: the spaces inside the quotes are part of the separator.

    Hint. Mind the spaces inside the quotes.