CONCAT

What it does

CONCAT(text1, [text2], ...)

CONCAT joins text end to end and returns the result as one text value. Nothing is inserted between the pieces, nothing is skipped, and the order is the order the arguments are written.

The property that makes it a new function rather than a rename of CONCATENATE is how it consumes a range. CONCAT flattens a multi-cell argument and joins every cell in it; CONCATENATE does not accept one. CONCAT(A1:A3) is a legal, meaningful call.

Stated as an operation: let each argument be expanded to a sequence of scalar values — an array or range expands in row-major order, a scalar expands to itself — and let each scalar be converted to text by the ordinary to-text rules. CONCAT returns the concatenation of all those texts, in argument order, with the expansions inlined in place. Empty cells contribute empty text, which is the same as contributing nothing.

Microsoft's article states the replacement relationship plainly: CONCAT "replaces the CONCATENATE function", though CONCATENATE remains available for backward compatibility.

Arguments

text1 — required; text2, … — optional, up to 253 text arguments in total per the article. Each argument may be a string or an array such as a cell range.

Two argument-level points worth stating:

• The 253 limit is on argument slots, not on cells. A single range argument can contribute far more than 253 values. This is the point readers most often get backwards when migrating from CONCATENATE, whose 255 slots each had to be written out.

Try it

Quirks

Result and edge cases

The return kind is Text.

• The 32,767-character cap is a real boundary, and it errors. Microsoft's article states that a result longer than 32,767 characters returns #VALUE!. That is the cell text limit from chapter 01, surfacing here as an error rather than as truncation — consistent with what that chapter records for the formula path generally.
• Empty cells inside a range contribute nothing, so CONCAT over a sparse column silently closes the gaps. There is no way to tell afterwards how many cells were empty.
• Numbers and logicals convert to text. A number contributes its General rendering, so a value displayed as 1 234,57 by a cell format contributes the unformatted digits. Wrap in TEXT when the rendering matters.
• Reference and array arguments are resolved and expanded before conversion, per chapter 03.

Errors

• #VALUE! when the joined result would exceed 32,767 characters (documented).
• An error value anywhere in the input propagates (chapter 02) — including an error sitting in one cell of a range argument, which is the case where CONCAT differs most sharply from an aggregate that could choose to skip it.
• A value kind with no text conversion surfaces #VALUE! under the shared coercion rules.

How to implement

Notes for implementers

1. Expansion order is part of the contract. Row-major within each argument, arguments in written order. Any other order is a different function, and the difference only shows on multi-row, multi-column ranges — which is exactly the shape casual tests omit.
2. Check the length cap incrementally. A single append that crosses 32,767 must produce the error rather than a truncated string; building the whole result and testing afterwards is correct but can allocate absurdly on adversarial input.
3. The cap counts UTF-16 code units, so a result made of astral characters reaches it at half the number of visible characters.

Rust code

29 concat_fn "CONCAT" (1, 254) [R "text1", R "text2"] |s, a, sv| { concat(s, a, sv) }

fn concat(site: &Site<'_>, a: &[CalcValue], sv: &mut crate::call::EvalServices<'_>) -> Step<CalcValue> {
    let mut joined: Vec<u16> = Vec::new();
    for index in 0..a.len() {
        let g = cells(site, a, index, sv)?;
        for value in &g.cells {
            let units = cell_text(site, index, value)?;
            if joined.len() + units.len() > EXCEL_TEXT_MAX_UTF16_CODE_UNITS {
                return fail(WorksheetErrorCode::Calc);
            }
            joined.extend_from_slice(&units);
        }
    }
    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

• CONCATENATE is the function CONCAT replaces; Microsoft retains it for workbook compatibility and its own article warns that it may not be available in future versions. The two differ in range handling, in argument limits, and — per the axis chip this page renders — in array lifting. See CONCATENATE.
• TEXTJOIN is the third member of the family and the one to reach for by default: it adds the delimiter and ignore-empty arguments that CONCAT deliberately lacks.
• & (FUNC.OP_CONCAT) is the operator form. For two or three scalars it is shorter and equivalent in intent; it has no range behaviour.
• TEXTSPLIT is the modern inverse direction.

Named variants

No named variants are recorded yet.