JOIN Function: Google Sheets’ Unique Function to Combine Cell Range Values with a Specified Delimiter

スポンサーリンク
スポンサーリンク

About the JOIN Function

Overview of JOIN

Combine Multiple Values with a Specified DelimiterGoogle Sheets Function

=JOIN( delimiter, array or list of values )

Summary The JOIN function concatenates multiple values into a single string using a specified delimiter.
  • You can combine multiple cells or values into one string.
  • By specifying a delimiter, such as a comma or space, you can format the data neatly.
  • Useful for data organization and adjusting output formats.
  • Equivalent to Excel’s TEXTJOIN function.

When to Use the JOIN Function

  • When you want to combine a list or array into a single string.
  • When you want to organize data with comma-separated or space-separated formatting.
  • When you want to improve the readability of your data.

How to Use JOIN

The following table explains the basic usage of the JOIN function.

  A B C D
1 Data1 Data2 Data3
2 Apple Banana Orange

=JOIN(“, “, A2:C2)

Result

  • The values in cells A2, B2, and C2 are combined with “, ” as the delimiter, resulting in:
  • Apple, Banana, Orange

Example 2: Concatenating Fixed Values

Here’s an example of concatenating specified values directly.

=JOIN(” – “, “New York”, “Los Angeles”, “Chicago”)

Result

  • The values “New York,” “Los Angeles,” and “Chicago” are combined with ” – ” as the delimiter, resulting in:
  • New York – Los Angeles – Chicago

Advanced Examples

Advanced Example 1: Excluding Blank Cells

Here’s how to concatenate only non-blank cells from a list.

  A B C D
1 Name   Occupation
2 Smith Engineer

=JOIN(” “, FILTER(A2:C2, A2:C2<>””))

Result

  • Non-blank cells “Smith” and “Engineer” are combined with a space as the delimiter, resulting in:
  • Smith Engineer

Points to Note

  • You can specify any string as the delimiter (e.g., space, comma, semicolon).
  • Blank cells are included by default; use the FILTER function to exclude blank cells if needed.
  • Performance may be affected when concatenating very large arrays.

Conclusion

  • The JOIN function is a convenient tool for combining multiple values into a single string.
  • Specifying a delimiter makes it suitable for organizing data.
  • Combining it with other functions (e.g., FILTER or ARRAYFORMULA) allows for flexible data processing.
スポンサーリンク