How to Use the JOIN Function in Google Sheets
=JOIN(delimiter, value_or_array1, [value_or_array2, ...]).
What JOIN does
You have a column of names and need them in one cell, separated by commas. Gluing them with & one at a time is slow. JOIN takes the whole range and a separator, and returns one text. It is the reverse of SPLIT, which cuts a text apart.
JOIN syntax
=JOIN(delimiter, value_or_array1, [value_or_array2, ...])
delimiter: the character or string placed between the joined values. It can be left blank, as inJOIN(,{1,2,3}).value_or_array1: the value or values to join.value_or_array2, ...: optional further values or arrays.
Per Google's help page, if the delimiter is omitted, the result is similar to what CONCATENATE produces. The input must be one-dimensional, such as a single column or row.
A step-by-step example
Here are three names in cells A1:A4.
| A | |
|---|---|
| 1 | Name |
| 2 | Ann |
| 3 | Ben |
| 4 | Cara |
=JOIN("-", A2:A4)
This returns Ann-Ben-Cara. Here is why: the delimiter is placed between each pair of values in the range. With a comma and a space:
=JOIN(", ", A2:A4)
This returns Ann, Ben, Cara.
JOIN or TEXTJOIN?
If your range has blank cells and you want them skipped, TEXTJOIN has an option for that. The JOIN help page does not describe how blanks are handled, so use TEXTJOIN when blanks matter.
Common mistakes
- Forgetting the delimiter. It is the first argument, and leaving it out joins everything together.
- Passing a two-dimensional range. Join one column or row at a time.
- Missing quotation marks around the delimiter.
Try it yourself
Use the names above (A2:A4). Type your formula into the box, starting with =, then press Check.
Exercise 1: Join with hyphens
Write a formula that joins the names in A2:A4 with a hyphen between them.
Exercise 2: Comma list
Write a formula that joins the names in A2:A4 separated by a comma and a space.
What to learn next
- HYPERLINK: create a clickable link with a formula.
- TEXTJOIN: combine text from many cells with a separator.
- SPLIT: break text into separate cells.
Syntax and rules in this guide were checked against Google's JOIN help page. See all guides.