How do I join values with a comma but skip empty cells?

Set the delimiter to "," and set ignore_empty to TRUE. For example, =TEXTJOIN(",", TRUE, A2:A10) will produce a comma-separated list that omits any blanks in the range.

Can TEXTJOIN handle numbers and dates without converting them first?

Yes. TEXTJOIN automatically coerces numbers, dates, and logical values to text. However, you may want to format them with TEXT() first if you need a specific presentation, such as currency symbols or date formats.

Why does TEXTJOIN return a #VALUE! error when I use a cell reference for the delimiter?

The cell referenced for the delimiter must contain a single text value. If the referenced cell contains a formula that returns an array, or if the reference points to multiple cells, TEXTJOIN cannot resolve a single delimiter and throws #VALUE!.

What’s the difference between TEXTJOIN and CONCAT in Excel?

TEXTJOIN lets you specify a delimiter and optionally ignore empty cells, making it ideal for creating readable lists. CONCAT simply concatenates all arguments without any separator and always includes empty strings, so you would need extra functions to mimic TEXTJOIN’s behavior.

TEXTJOIN function

TEXTJOIN merges multiple text strings or ranges into one string, using a chosen delimiter and optional ignoring of empty cells.

=TEXTJOIN(delimiter, ignore_empty, text1, ...)

Generate a TEXTJOIN formula

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

Arguments

How TEXTJOIN reads its arguments

Argument	Required	Description
delimiter	Required	A text string (or reference to a single cell) that separates each joined element; if omitted or an array, TEXTJOIN returns a #VALUE! error.
ignore_empty	Required	A logical TRUE/FALSE that tells TEXTJOIN whether to skip blank cells; TRUE omits them, FALSE includes empty strings as consecutive delimiters.
text1	Required	The first text argument; can be a string, a cell reference, or a range of cells containing text or numbers, which are coerced to text.
...	Repeating	Additional text arguments or ranges, evaluated in order; each may be a literal, a cell, or an array, and are all joined using the same delimiter.

Returns

Returns a single text string that concatenates the supplied values.

Availability

Excel: 2019+ · Google Sheets: Supported

Worked examples

1. List all campaign names, comma-separated

Campaign	Channel	Spend	Clicks	Conversions
Spring Sale	Email	5000	1200	300
Spring Sale	Social	3000	1500	250
Summer Launch	Search	7000	2000	400

=TEXTJOIN(", ", TRUE, A3:A5)

Result: Spring Sale, Spring Sale, Summer Launch

The formula takes the range A3:A5, which holds the three campaign names, and joins them with a comma and space. Because ignore_empty is TRUE, any blank cells would be skipped, though none exist here. The result is a single string listing each campaign in the order they appear.

2. Create a pipe-delimited "Campaign: $Spend" list

Campaign	Channel	Spend	Clicks	Conversions
Spring Sale	Email	5000	1200	300
Spring Sale	Social	3000	1500	250
Summer Launch	Search	7000	2000	400

=TEXTJOIN(" | ", TRUE, A3:A5 & ": $" & C3:C5)

Result: Spring Sale: $5000 | Spring Sale: $3000 | Summer Launch: $7000

Each row’s campaign name (A3:A5) is concatenated with a colon, a space, a dollar sign, and the spend amount (C3:C5). TEXTJOIN then merges those three combined strings using " | " as the delimiter. Empty cells are ignored, but the dataset has none, so every row appears in the final pipe-separated list.

3. Line-break list of conversions for high-click rows

Campaign	Channel	Spend	Clicks	Conversions
Spring Sale	Email	5000	1200	300
Spring Sale	Social	3000	1500	250
Summer Launch	Search	7000	2000	400

=TEXTJOIN(CHAR(10), TRUE, FILTER(E3:E5, D3:D5>1500))

Result: 250 400

FILTER extracts conversion values (E3:E5) only where clicks (D3:D5) exceed 1500, yielding 250 and 400. TEXTJOIN then stitches those numbers together with CHAR(10), the line-feed character, producing a multi-line string. Because ignore_empty is TRUE, any rows that didn’t meet the click threshold are omitted entirely.

Common errors

Which TEXTJOIN error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The delimiter argument is a multi-cell range instead of a single text value.	Replace the range with a single cell reference or a literal string, e.g., "," or $A$1.
#N/A	One of the text arguments evaluates to an error such as a failed VLOOKUP, and TEXTJOIN propagates that error.	Wrap the problematic argument in IFERROR (or IFNA) to supply a fallback value before joining.
#SPILL!	When TEXTJOIN is entered in a cell that already contains data and the result would spill into adjacent cells, the dynamic array engine blocks it.	Move the formula to an empty cell with enough surrounding space, or use @ to force a single-cell result.

Tips and when to use something else

Use TRUE for ignore_empty when you have blank rows or columns you don’t want extra delimiters for.
If you only need to join two strings, CONCAT or CONCATENATE are simpler and avoid the delimiter argument.
CHAR(10) creates line breaks on Windows; use CHAR(10) inside TEXTJOIN for multi-line output.
When joining large ranges, wrap the range in TRIM to remove leading/trailing spaces that would otherwise appear in the final string.

Related functions

CONCATCONCAT joins two or more text strings into one continuous string, automatically coercing numbers and dates to text without a delimiter.CONCATENATECONCATENATE joins several text strings into one continuous string, in the order the arguments are supplied, and returns it as a single text value.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.

Frequently asked questions

How do I join values with a comma but skip empty cells?: Set the delimiter to "," and set ignore_empty to TRUE. For example, =TEXTJOIN(",", TRUE, A2:A10) will produce a comma-separated list that omits any blanks in the range.
Can TEXTJOIN handle numbers and dates without converting them first?: Yes. TEXTJOIN automatically coerces numbers, dates, and logical values to text. However, you may want to format them with TEXT() first if you need a specific presentation, such as currency symbols or date formats.
Why does TEXTJOIN return a #VALUE! error when I use a cell reference for the delimiter?: The cell referenced for the delimiter must contain a single text value. If the referenced cell contains a formula that returns an array, or if the reference points to multiple cells, TEXTJOIN cannot resolve a single delimiter and throws #VALUE!.
What’s the difference between TEXTJOIN and CONCAT in Excel?: TEXTJOIN lets you specify a delimiter and optionally ignore empty cells, making it ideal for creating readable lists. CONCAT simply concatenates all arguments without any separator and always includes empty strings, so you would need extra functions to mimic TEXTJOIN’s behavior.

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