TEXTJOIN
Joins values with a delimiter and can skip empty cells: builds an address from its parts or a comma-separated list.
TEXTJOIN(delimiter, ignore_empty, text1, ...)- delimiter
- What to put between the pieces: ", ", " " or "; ". Written in quotes.
- ignore_empty
- TRUE skips empty cells, FALSE keeps them and adds an extra delimiter. You almost always need TRUE.
- text1
- What to join: a range, cells or text in quotes. You can list several, separated by commas.
Return value
Text: the values joined with the delimiter. If the second argument is TRUE, empty cells are skipped.
Examples
Address from its parts
fx
A
B
C
D
E
F
1
2
3
4
5
6
Street
City
State
ZIP
Address
125 Main St
Austin
TX
78701
350 Oak Ave
Denver
CO
42 Pine Rd
Portland
OR
97205
Sheet1
Details
TEXTJOIN is like CONCAT, but it puts the delimiter between the pieces for you. The delimiter goes first, in quotes, for example ", ".
The second argument decides what to do with empty cells. TRUE skips them. FALSE keeps them, and then delimiters pile up: "350 Oak Ave, Denver, CO, ".
It's easiest to pass a whole range: =TEXTJOIN(", ",TRUE,A2:D2).
Common mistakes
- A delimiter without a space: "," gives "125 Main St,Austin". Usually you need ", ".
- FALSE in the second argument: empty cells produce double commas.
- Arguments out of order. First the delimiter, then whether to skip empty cells, then what to join.