CONCATENATE

What it does

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

CONCATENATE joins two or more text strings into one string, in argument order, with nothing inserted between them.

It is the legacy member of its family. Microsoft's article states that it "has been replaced with the CONCAT function", recommends CONCAT going forward, and warns that CONCATENATE "may not be available in future versions of Excel". Excel keeps the name so that existing workbooks keep calculating; new formulas should use CONCAT, TEXTJOIN, or &.

The substantive difference from CONCAT is not the name. CONCATENATE is scalar-shaped: each argument contributes one value, not a flattened range. A multi-cell range handed to CONCATENATE does not join its cells.

Arguments

text1 — required, the first item to join; may be text, a number, or a cell reference. text2, … — optional. The article states the ceiling as up to 255 items totalling a maximum of 8,192 characters.

Note that those are two different limits from the ones CONCAT carries (253 slots, 32,767 characters), and the 8,192-character figure is documented for CONCATENATE specifically. If you are migrating formulas between the two functions, both limits change.

The argument position that most often surprises readers is any position holding a multi-cell range. CONCAT accepts it and flattens; CONCATENATE does not consume it as a range at all, and what happens instead depends on the shared array/implicit-intersection machinery rather than on this function.

Try it

Quirks

Result and edge cases

The return kind is Text.

• Numbers and logicals convert to text by the ordinary rules; a number contributes its General rendering, not its displayed format.
• Empty cells contribute empty text.

Errors

• An error value in any argument propagates (chapter 02).
• A value kind with no text conversion surfaces #VALUE! under the shared coercion rules.
• Microsoft's article documents #NAME? as the usual symptom of a common authoring mistake — missing quotation marks around a text argument. That is a formula-entry problem rather than a behaviour of the function, and it is listed here because the article lists it and readers arrive looking for it.

The article's other two "common problems" are also authoring issues rather than function semantics: quotation marks appearing in the result usually mean a missing comma between arguments, and words running together mean no space argument was supplied. CONCATENATE inserts nothing on its own.

How to implement

Notes for implementers

1. Do not implement CONCATENATE as CONCAT with a different arity. The range behaviour and the position-dependent lifting are genuinely different, and the difference is observable in ordinary workbooks.
2. Carry both documented limits separately (255 slots, 8,192 characters here; 253 and 32,767 there). They are not derived from a shared constant.
3. The lift positions are irreducible structure. The [0,1,2] position list is not a rule with a reason; it is a fact to be copied, and the call-pipeline chapter says so about this whole axis.

Rust code

28 concatenate_fn "CONCATENATE" (1, 255) [V "text1", V "text2"] |s, a, sv| { concatenate(s, a) }

fn concatenate(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let mut joined: Vec<u16> = Vec::new();
    for index in 0..a.len() {
        let units = cell_text(site, index, &a[index])?;
        joined.extend_from_slice(&units);
    }
    // A result longer than a cell holds is cut to its first 32,767 UTF-16 code units, as &'s is
    // (dc-fl2, Excel 16.0.20527: LEN(CONCATENATE(REPT("a",32767),"b")) is 32,767; CONCAT's is
    // #CALC!).
    joined.truncate(EXCEL_TEXT_MAX_UTF16_CODE_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

• CONCAT is the modern replacement, named as such by Microsoft. It accepts ranges and carries different limits. See CONCAT.
• TEXTJOIN adds a delimiter and an ignore-empty flag, and is usually the better migration target for a CONCATENATE chain that interleaves separator literals.
• & (FUNC.OP_CONCAT) is the operator form; the article itself recommends it as the simpler syntax. It is not identical in shape to either function — it is a binary operator with the operator pipeline's own lifting behaviour.
• TEXT is what the article recommends composing with when the joined pieces need formatting; CONCATENATE will not format anything for you.

Despite its status, CONCATENATE is not in Excel's Compatibility category. It sits in the Text category alongside CONCAT, and Microsoft's deprecation statement is in prose rather than in the category taxonomy.

Named variants

No named variants are recorded yet.