When you need to stitch a range of values into a single string with a delimiter between each, the JOIN function in Google Sheets does it in one call. This article walks through four practical examples with the formulas and outputs you can copy straight in.
JOIN Function Syntax in Google Sheets
The function takes a delimiter and one or more ranges of values.
=JOIN(delimiter, value_or_array1, [value_or_array2, ...])
- delimiter – the character or string placed between each value in the output
- valueorarray1 – the first value or range whose contents get joined
- valueorarray2 – additional values or ranges to append in order, [optional]
When to Use JOIN Function
- Building a single comma-separated string from a column of names or IDs
- Creating space-separated lists for pasting into other tools
- Producing pipe-delimited or custom-delimited output for exports
- Stitching values from two or more columns into one string
- Quickly previewing all entries in a range as one cell
Example 1: Join a column of names with a comma
Let’s start with the most common case, gluing a list of names together with commas.
Below is the dataset, a single column of five names sitting in A2 to A6.
The goal is to get one comma-separated string of all five names in cell B2.
Here is the formula:
=JOIN(",", A2:A6)
The first argument is the delimiter, a comma in quotes. The second argument is the range A2:A6. JOIN walks down the range and puts a comma between each name.
Pro Tip: TEXTJOIN is the more modern alternative and supports a skip-blanks argument, which JOIN does not. For just two values, the & operator also works (e.g., A2 & “,” & A3).
Example 2: Join cities into a sentence with spaces
A space delimiter is handy when you want the output to read like a list of words rather than a CSV row.
Below is the dataset, a column of city names from A2 to A6.
You want all five cities placed in one cell with a single space between each.
Here is the formula:
=JOIN(" ", A2:A6)
The space character inside the quotes is the delimiter. JOIN drops a space between each city as it walks the range.
The output shows Mumbai Delhi Pune Chennai Kolkata as a single line of text.
Example 3: Join product codes with a pipe delimiter
A multi-character delimiter is useful when you need a clear divider that won’t show up inside your values.
Below is the dataset, four product codes sitting in A2 to A5.
The goal is to combine all four codes into one string with a space-pipe-space between each.
Here is the formula:
=JOIN(" | ", A2:A5)
The delimiter here is three characters, a space, pipe, space. JOIN treats the entire string as one separator and drops it between every pair.
The result reads AB100 | CD200 | EF300 | GH400 in cell B2.
Example 4: Join values from two separate ranges
JOIN accepts more than one range, which is helpful when the values you want to combine live in different columns.
Below is the dataset, with first names in column A and last names in column B, rows 2 through 5.
The goal is to combine both columns into one comma-separated string, all first names followed by all last names.
Here is the formula:
=JOIN(",", A2:A5, B2:B5)
How this formula works:
- JOIN reads the first range A2:A5 top to bottom, then moves to the second range B2:B5.
- A comma is placed between every value across both ranges.
- The order is range one first, then range two, so all first names come before any last name.
The output ends up as John,Mary,Raj,Anna,Smith,Jones,Patel,Lee in cell C2.
Tips & Common Mistakes
- Empty cells still get joined. JOIN keeps blank cells in place, which can leave you with double delimiters like ,, in the output. Use TEXTJOIN with the skip-blanks flag if that’s a problem.
- The delimiter must be in quotes. Even a single comma or space has to sit inside double quotes. Skip the quotes and you’ll get a parse error.
- Pair with LEN function for length checks. If you need to know the character count of the joined string for an export limit, wrap JOIN inside LEN.
JOIN is the simplest way to flatten a range into one string in Google Sheets. It works with any delimiter, including multi-character ones, and accepts several ranges in a single call. Pick TEXTJOIN if you also need to skip blanks.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: