Use JOIN in Google Sheets to combine a row or column into one cell with a separator. For example, =JOIN(", ",A2:A6) creates a comma-and-space-separated list.
Join a Column of Names with Commas
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Name |
|---|
| Maya |
| Noah |
| Ari |
| Lena |
| Omar |
Enter this formula in E2:
=JOIN(", ",A2:A6)E2 displays Maya, Noah, Ari, Lena, Omar. The delimiter appears between names, with no extra delimiter before the first name or after the last.
JOIN Function Syntax
=JOIN(delimiter, value_or_array1, [value_or_array2, ...])The delimiter is your separator. Each value argument can be a single value, one row, or one column. Additional arguments are optional; the square brackets are not part of the formula.
Put literal text separators in double quotes. You can also reference a cell containing the separator. Use "" for no separator: =JOIN("",A2:A3) returns MayaNoah.
Join Cities with Spaces
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| City |
|---|
| Mumbai |
| Delhi |
| Pune |
| Chennai |
| Kolkata |
=JOIN(" ",A13:A17)The result is Mumbai Delhi Pune Chennai Kolkata. JOIN preserves the order of the input cells. It does not sort the list or remove duplicates.
Join Product Codes with a Pipe Delimiter
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| Code |
|---|
| AB100 |
| CD200 |
| EF300 |
| GH400 |
=JOIN(" | ",A23:A26)The output is AB100 | CD200 | EF300 | GH400. The entire three-character separator, including both spaces, is inserted between each pair of codes.
Join Two Ranges or Combine First and Last Names
Enter this dataset starting in A32. Leave cells marked “(blank)” empty.
| First | Last |
|---|---|
| John | Smith |
| Mary | Jones |
| Raj | Patel |
| Anna | Lee |
=JOIN(",",A33:A36,B33:B36)This returns John,Mary,Raj,Anna,Smith,Jones,Patel,Lee. JOIN reads all of the first range, then all of the second. It does not pair first names with surnames row by row.
To create one full name for the first row, use:
=JOIN(" ",A33,B33)This returns John Smith. Fill the formula down in another column to combine each row separately. The ampersand operator is another option for combining a small number of cells.
Use one-dimensional ranges. =JOIN(",",A33:B36) returns #VALUE! because that argument has multiple rows and columns. Pass the columns separately, or use TEXTJOIN for a rectangular block.
Handle Blank Cells and Check the Output Length
Enter this dataset starting in A42. Leave cells marked “(blank)” empty.
| Entry |
|---|
| Apple |
| (blank) |
| Pear |
=JOIN(",",A43:A45)The blank middle cell produces Apple,,Pear. JOIN has no ignore-empty argument. If the blank should be omitted, use:
=TEXTJOIN(",",TRUE,A43:A45)That returns Apple,Pear. For an export length check, =LEN(JOIN(",",A43:A45)) returns 11, including the two commas.
A source error also affects the joined result. Fix the error where it occurs instead of assuming JOIN skips it. Joining values creates text, so use SUM when you want numeric addition.
Other Google Sheets articles you may also like