Can JOIN handle both individual cells and array ranges at the same time?

Yes. JOIN accepts a mix of individual cell references and ranges. You can write =JOIN(", ", A1, B1:B5, C1) to join a single cell, a range, and another single cell in the same formula.

What happens if I use a special character or symbol as the delimiter?

The delimiter is included exactly as written in the output. For example, =JOIN(" → ", A1, B1) produces output with an arrow between the values. You can use any text, emoji, or symbol Google Sheets recognizes.

Can I create multi-line text with JOIN?

Yes, use CHAR(10) as the delimiter to insert line breaks: =JOIN(CHAR(10), A2, B2, C2) produces text that wraps to multiple lines when the cell is formatted for text wrapping.

How do I join only the non-empty cells in a range?

Combine JOIN with FILTER: =JOIN(", ", FILTER(A2:A10, A2:A10<>"")). This joins only cells that are not empty, skipping blank cells in your range.

JOIN function

Concatenates multiple text values or array elements into a single string, separated by a delimiter you specify.

=JOIN(delimiter, value1, ...)

Generate a JOIN formula

Describe what you need. The generator will reach for JOIN where JOIN is the right tool, and tell you when it is not.

Arguments

How JOIN reads its arguments

Argument	Required	Description
delimiter	Required	Required. The text string to place between values. Can be any text, including empty string, spaces, or multi-character sequences like ' \| '.
value1	Required	Required. The first value to join, which may be a single cell, array range, or literal value. Non-text values are automatically converted to text.
...	Repeating	Optional. Additional values to concatenate, repeating the pattern. JOIN accepts unlimited arguments and processes both ranges and individual cell references.

Returns

Returns a text string with all input values joined in order using the specified delimiter as separator.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Create a customer profile summary

Customer	Plan	MRR
Acme Corp	Pro	499

=JOIN(" - ", A2, B2, "$" & C2)

Result: Acme Corp - Pro - $499

Joins the customer name, plan tier, and MRR into a single profile line using hyphens as separators. The ampersand operator adds a dollar sign to the MRR numeric value before joining.

2. Build a formatted billing record with dates

Customer	Plan	MRR	Signup Date
Beta Inc	Standard	199	2024-02-20

=JOIN(" | ", A3, B3, TEXT(C3, "$0"), TEXT(D3, "MM/DD/YY"))

Result: Beta Inc | Standard | $199 | 02/20/24

Uses JOIN to build a pipe-separated record combining customer name, plan, currency-formatted MRR, and a date reformatted from YYYY-MM-DD to MM/DD/YY notation for a billing report.

3. Combine multiple customers into a comma-separated list

Customer

Acme Corp

Beta Inc

Gamma Ltd

=JOIN(", ", A2:A4)

Result: Acme Corp, Beta Inc, Gamma Ltd

References a range A2:A4 containing three customer names and joins them into a single comma-separated string. This demonstrates that JOIN works with array ranges, not just individual cell references.

Common errors

Which JOIN error are you seeing?

Error	Why it happens	How to fix it
#REF!	One of the value arguments references a cell or range that has been deleted or is no longer available.	Update the formula to reference existing cells. If you deleted a column, adjust the cell references to point to the correct remaining columns.
#NAME?	The function name is misspelled (e.g., JOINS or JOI) or JOIN is not available in your version of Google Sheets.	Check the spelling of JOIN and ensure your Google Sheets version supports it. Older versions or specific accounts may not have this function available.
#VALUE!	The delimiter argument is set to a cell containing an error value, or values are in a format that cannot be converted to text.	Verify that the delimiter is a valid text string and not an error. Ensure all value arguments reference cells with convertible content, not formulas returning errors.

Tips and when to use something else

For large datasets, use JOIN with array ranges like A2:A100 instead of individual cells to reduce formula complexity.
If you need to split the joined text back into separate values later, pair JOIN with SPLIT function for reversible text transformations.
When combining multiple columns for reports, consider XLOOKUP or INDEX/MATCH first—sometimes a reference lookup is simpler than manual concatenation.
Use CHAR(10) as a delimiter to create multi-line text: =JOIN(CHAR(10), A2, B2) produces text that breaks across rows when displayed.

Related functions

SPLITSPLIT breaks a text string into an array of substrings by dividing on a specified delimiter or by individual characters.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

Can JOIN handle both individual cells and array ranges at the same time?: Yes. JOIN accepts a mix of individual cell references and ranges. You can write =JOIN(", ", A1, B1:B5, C1) to join a single cell, a range, and another single cell in the same formula.
What happens if I use a special character or symbol as the delimiter?: The delimiter is included exactly as written in the output. For example, =JOIN(" → ", A1, B1) produces output with an arrow between the values. You can use any text, emoji, or symbol Google Sheets recognizes.
Can I create multi-line text with JOIN?: Yes, use CHAR(10) as the delimiter to insert line breaks: =JOIN(CHAR(10), A2, B2, C2) produces text that wraps to multiple lines when the cell is formatted for text wrapping.
How do I join only the non-empty cells in a range?: Combine JOIN with FILTER: =JOIN(", ", FILTER(A2:A10, A2:A10<>"")). This joins only cells that are not empty, skipping blank cells in your range.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17