Text concatenation (&)

What it does

text1 & text2

A & B converts both operands to text and returns their concatenation, left operand first.

The whole difficulty of this operator is hidden inside "converts to text", because that conversion is a rendering, and rendering is where locale and formatting live:

• Numbers. Rendered by general formatting rules (chapter 02). This is not the cell's display format: a cell showing 1 234,50 € concatenates as whatever general formatting makes of the underlying number, not as what the cell shows. The decimal separator used in that rendering is locale-dependent, and chapter 02 flags the locale-dependent surface as an open area — so =1.5&"" is not guaranteed to be "1.5" everywhere.
• Logicals. Render as the text TRUE and FALSE — in English, on the observed baseline. Whether that rendering localizes is not recorded here.
• Dates and times. A date is a number wearing a format (chapter 01), so & renders the serial number, not the date. =A1&"" on a date cell gives the serial, which is the single most common surprise on this operator and the reason TEXT(A1,"yyyy-mm-dd") exists.
• Empty. Concatenating a blank contributes nothing, which is why =A1&"" is the standard idiom for "give me the text of this cell, or an empty string".

Chapter 01's text model applies to the result: worksheet text is a sequence of UTF-16 code units capped at 32,767 units, and it records that producing an over-cap string in a formula path yields #VALUE! where the interop path truncates. & is the easiest way in the whole function surface to reach that cap.

Arguments

Arity is exactly 2; no optional arguments, no defaults. Order is load-bearing — concatenation is not commutative. A chain a & b & c is two applications of this two-argument operator, and because concatenation is associative, the grouping does not change the result.

Try it

Quirks

Result and edge cases

• Blank operands. Contribute the empty string.
• Error operands. Propagate; & does not render an error as its display text. =NA()&"" is #N/A, not "#N/A".
• Arrays. The family's broadcast rule applies, and the contract's findings show it clearly: ={"a","b"}&{"x";"y"} produces the 2×2 outer product, and ={"a","b"}&{"x","y","z"} produces two joined values plus #N/A at the coordinate the left operand cannot supply. Concatenating a row against a column is therefore a compact way to build a full cross-product of labels.
• The 32,767-unit cap. Reachable by repeated concatenation. See chapter 01 for the formula-path-versus-interop-path split, which is an observed distinction rather than a documented one.

Errors

data/functions/FUNC.OP_CONCAT.json records no Microsoft documentation URL (docs is null).

How to implement

Notes for implementers

• The number-to-text rendering is the entire compatibility surface of this operator. Pin it explicitly: significant digits, when scientific notation kicks in, how integers and negative zero render, and what the decimal separator is. A "close enough" rendering produces text that differs from Excel's in ways that are trivially visible to users and fatal to comparisons.
• Do not render errors. An error operand short-circuits to the error value.
• Count the cap in UTF-16 code units, not characters and not bytes (chapter 01). Astral characters count double.
• Concatenation is associative, so chains may be evaluated in any grouping — but the rendering of each operand must happen before joining, not after, or a numeric operand could be rendered in a different context.

Rust code

entries: &[CatalogueEntry::operator(
    CONCATENATE,
    "&",
    Notation::Infix,
    TEXTS,
    concatenate,
)

fn concatenate(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(CONCATENATE, services);
    Site::finish(joined(&site, arguments))
}

/// The operands' text joined, a number's text as coercion gives it. An operand's error is the
/// result, the left operand's first, before any operand is read as text: no operand's text can be
/// an error. A result longer than a cell holds is cut to its first 32767 UTF-16 code units, even
/// where the cut parts a surrogate pair. Only a top-level & in a cell was measured: the
/// mixed_operands rows of cfunc-m1.2's oracle workbook =C2&"b", with 32767 characters in C2, and
/// C1, with 32766 characters, joined with a text of the one character U+1F600. The cut of an &
/// whose result another operator or function reads is unmeasured.
fn joined(site: &Site<'_>, arguments: &[CalcValue]) -> Step<CalcValue> {
    site.scalar_operands(arguments)?;
    if let Some(code) = arguments.iter().find_map(|argument| match argument.core_ref() {
        CoreRef::Error(code) => Some(code),
        _ => None,
    }) {
        return Err(Exit::Error(code));
    }
    let left = site.argument(0, coerce::text(&arguments[0]))?;
    let right = site.argument(1, coerce::text(&arguments[1]))?;
    let mut units = Vec::with_capacity(left.len() + right.len());
    units.extend_from_slice(&left);
    units.extend_from_slice(&right);
    // Measured for a top-level & only (see above).
    units.truncate(EXCEL_TEXT_MAX_UTF16_CODE_UNITS);
    Ok(CalcValue::text(ExcelText::from_utf16_code_units(units)))
}

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 — the legacy function form. data/functions/FUNC.CONCATENATE.json places it in the Compatibility category: Microsoft's modern replacement is CONCAT, and the old name is retained so existing workbooks keep working. New formulas should use CONCAT or TEXTJOIN; CONCATENATE is kept for workbook compatibility, not recommended. (https://support.microsoft.com/en-us/office/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d)
• CONCAT — the modern replacement, which accepts ranges as well as scalars. (https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)
• TEXTJOIN — the delimiter-aware, empty-skipping join. (https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)
• & is not superseded by any of them. It remains the operator; the functions are alternatives with extra arguments, not replacements.
• TEXT, VALUE, T, N — explicit conversion. If the rendering matters, TEXT states the format instead of inheriting the general one.
• EXACT — because the natural follow-up to building text with & is comparing it, and = on text is case-insensitive.

Named variants

No named variants are recorded yet.