Excel track · Exercise 63 of 86 Paid
TEXTJOIN: join a range and skip blanks
Join a column into one line and decide what happens to the blanks.
Intermediate
Five people, three departments
Read-only data · answer in B7
In B7, join the departments in B2:B6 into one line, separated by a comma and a space, skipping the blanks.
| A | B | |
|---|---|---|
| 1 | Name | Department |
| 2 | Ada | Engineering |
| 3 | Grace | |
| 4 | Alan | Research |
| 5 | Katherine | |
| 6 | Mary | Ops |
| 7 | Departments |
Reference answer
What it does
TEXTJOIN takes a separator, a blank rule and a range, and returns one string. The separator is text in quotes, a comma and a space here. The blank rule takes TRUE or FALSE.
Five rows sit in B2:B6 and three hold a department: Engineering, Research and Ops. TRUE drops the two empty slots, so the line reads Engineering, Research, Ops. FALSE keeps them, and the line carries a comma pair with nothing between.
CONCAT has neither argument, so it joins the same range with no separators at all. Because the separator sits in the formula here, a switch to semicolons is one character in one cell.
Syntax
=TEXTJOIN(delimiter, ignore_empty, range)Worked example
On Five people, three departments, =TEXTJOIN(", ",TRUE,B2:B6) returns Engineering, Research, Ops.
=TEXTJOIN(", ",TRUE,B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The joined text was used in arithmetic, as when the result is divided by a number. |
#NAME? | TEXTJOIN is typed without its last letter, and Excel returns #NAME?. |
Blanks kept | FALSE in the second argument keeps the two empty rows, so the line has two comma pairs with nothing between them. |
At work
A summary line from a column of tags or roles, where some rows are empty.