TEXTJOIN

What it does

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) flattens its text arguments into one sequence of items and concatenates them with delimiter between consecutive items.

Two things distinguish it from CONCAT and from the & operator, and both are in the signature:

1. The delimiter goes between items, not after them. n items produce n − 1 separators. Getting this right by hand — trimming a trailing comma off a & chain — is the chore the function exists to remove.
2. ignore_empty decides whether empty items count as items. With TRUE, empty cells contribute nothing and produce no separator; with FALSE, they contribute an empty item and therefore still produce a separator, yielding runs of adjacent delimiters. This is the whole reason the argument is mandatory rather than optional: there is no defensible default.

Ranges and arrays are flattened rather than treated as single values, so TEXTJOIN(", ", TRUE, A1:A10) is a ten-item join, not a one-item one. That flattening is the function's real power and the reason it replaced the &-chain idiom.

Arguments

An empty delimiter reduces the function to plain concatenation, which Microsoft names as a supported use.

Try it

Quirks

Result and edge cases

Returns Text.

Documented: the 32,767-character ceiling on the result, and #VALUE! when it is exceeded — the same cap and the same enforcement side as REPT, and the reason those two are the text functions most likely to hit it in practice. TEXTJOIN over a large range reaches the cap easily.

Open rather than settled:

• The Empty versus empty-text distinction. ignore_empty is documented in terms of empty cells. Whether an empty string argument, or an empty string inside an array literal, is also skipped is a different question under the shared model, which keeps Empty and text distinct (The value universe). Unverified.
• Non-text items. Numbers, dates and logicals in the item list must be rendered to text, and which rendering is used — general format, or something else — is a to-text question the shared model states only in outline.
• Error items. Whether an error in one of the joined cells propagates or is skipped. Propagation is the default under the shared rules, but TEXTJOIN scans ranges, and range scanning is where per-family policy lives.
• An array delimiter. Whether a multi-item delimiter is cycled across the joins, or only its first element is used, is a behaviour readers report and the Handbook has not checked.

Errors

How to implement

Notes for implementers

The separator rule is "between", which in practice means the emit loop must track whether anything has been emitted yet rather than appending a delimiter after each item and trimming. The trim approach breaks as soon as a legitimate item ends with the delimiter.

ignore_empty interacts with that flag: a skipped item must not advance the "something has been emitted" state, or the result gains leading or doubled separators.

The 32,767-unit cap has to be checked as the result grows, not after it is built. TEXTJOIN over a large range can otherwise allocate far past the cap before discovering the documented #VALUE!.

Counting for the cap is in UTF-16 code units, consistent with LEN and the value-universe chapter.

Rust code

30 textjoin_fn "TEXTJOIN" (3, 252) [R "delimiter", V "ignore_empty", R "text1", R "text2"] |s, a, sv| {
    textjoin(s, a, sv)
}

/// TEXTJOIN's delimiters: one text, or each cell of a reference or an array in turn, used in
/// order and again from the first (Microsoft's TEXTJOIN page: a range of delimiters). A reference
/// of several areas (a 3-D reference) gives the cells of each area in its order (Excel 16.0.20430,
/// 2026-10-25, dc-6au, sheets Ja, Fe and Ma holding 1, 2, 3 in A1 and 100, 200, 300 in A2:
/// `=TEXTJOIN(Ja:Ma!A1,1,"a","b")` "a1b", `=TEXTJOIN(Ja:Ma!A1:A2,1,"a","b","c")` "a1b100c",
/// `=TEXTJOIN(Fe:Ma!A1,1,"a","b","c")` "a2b3c").
fn delimiters(site: &Site<'_>, a: &[CalcValue], sv: &mut crate::call::EvalServices<'_>) -> Step<Vec<Vec<u16>>> {
    let mut found = Vec::new();
    match a[0].core_ref() {
        CoreRef::Reference(reference) => {
            for &area in reference.areas() {
                let one = [CalcValue::reference(crate::value::ReferenceLike::area(area))];
                for value in &grid(site, &one, 0, sv)?.cells {
                    found.push(cell_text(site, 0, value)?);
                }
            }
        }
        CoreRef::Array(_) => {
            for value in &cells(site, a, 0, sv)?.cells {
                found.push(cell_text(site, 0, value)?);
            }
        }
        _ => found.push(text_arg(site, a, 0)?.utf16_code_units().to_vec()),
    }
    Ok(found)
}

fn textjoin(site: &Site<'_>, a: &[CalcValue], sv: &mut crate::call::EvalServices<'_>) -> Step<CalcValue> {
    let delimiters = delimiters(site, a, sv)?;
    let ignore_empty = num(site, a, 1)? != 0.0;
    let mut joined: Vec<u16> = Vec::new();
    let mut parts = 0usize;
    for index in 2..a.len() {
        let g = cells(site, a, index, sv)?;
        for value in &g.cells {
            if ignore_empty && matches!(value.core_ref(), CoreRef::Empty | CoreRef::Missing) {
                continue;
            }
            let units = cell_text(site, index, value)?;
            if ignore_empty && units.is_empty() {
                continue;
            }
            let delimiter: &[u16] = if delimiters.is_empty() {
                &[]
            } else {
                &delimiters[parts.saturating_sub(1) % delimiters.len()]
            };
            let extra = units.len() + if parts == 0 { 0 } else { delimiter.len() };
            if joined.len() + extra > EXCEL_TEXT_MAX_UTF16_CODE_UNITS {
                return fail(WorksheetErrorCode::Calc);
            }
            if parts > 0 {
                joined.extend_from_slice(delimiter);
            }
            joined.extend_from_slice(&units);
            parts += 1;
        }
    }
    Ok(CalcValue::text(ExcelText::from_utf16_code_units(joined)))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CONCAT is the same flattening concatenation without a delimiter and without ignore_empty. CONCATENATE is the Compatibility-category ancestor of CONCAT: Microsoft has replaced it with CONCAT and retains the old name so existing workbooks keep working. TEXTJOIN is not part of that supersession chain — it is a distinct, later function, not a renamed one, and nothing it does is deprecated.
• The & operator is the two-item primitive. Everything above is sugar over it, and TEXTJOIN is the sugar that also solves the trailing-separator and empty-item problems.
• TEXTSPLIT is the inverse: delimiter-separated text back into an array. TEXTJOIN and TEXTSPLIT round-trip only when no item contains the delimiter — the classic escaping problem, which neither function addresses.
• REPT shares the result cap and its #VALUE!.
• TEXT is the usual supplier of formatted items to a TEXTJOIN call.

Named variants

No named variants are recorded yet.