TRIM

What it does

TRIM(text)

TRIM is not the trim you know from other languages. Most languages' trim removes leading and trailing whitespace and leaves the interior alone. Excel's TRIM also collapses interior runs.

The rule, stated exactly: take the text, delete every leading space, delete every trailing space, and replace each remaining run of consecutive spaces with a single space. Equivalently — and this is the formulation to implement — split the text on spaces, discard empty pieces, and rejoin with one space between pieces.

Two things follow that catch people out:

1. TRIM is not idempotent-by-accident, it is idempotent by construction. TRIM(TRIM(x)) is TRIM(x), because the output contains no leading, trailing, or doubled spaces by definition.
2. TRIM changes the interior of your data. If your text is fixed-width or contains deliberate alignment padding, TRIM destroys it. SUBSTITUTE(x, " ", "") removes spaces; TRIM normalises them; they are different tools and neither is a general "clean up" function.

Arguments

TRIM(text)

There is exactly one argument and no options. There is no way to ask TRIM to strip the ends without collapsing the interior, and no way to widen its notion of whitespace.

Non-text arguments convert to text first, by the ordinary rules in coercion and lifting: a number arrives as its general-format rendering, TRUE as the string TRUE. Since none of those renderings contains a space that a naïve reading would expect to survive, TRIM of a non-text scalar is generally that scalar's text form unchanged.

Try it

Quirks

Result and edge cases

The return kind is Text. TRIM of the empty string is the empty string; TRIM of a string of nothing but spaces is the empty string.

Other boundaries:

• An empty referenced cell delivers Empty, distinct from an omitted argument; see the value universe.
• Errors propagate. TRIM has no error-masking policy.
• Astral characters are unaffected, but note that the model's text is a sequence of UTF-16 code units, so a TRIM implemented over code units and one implemented over Unicode scalars will agree here only because no space character is astral. Do not generalise from that to the rest of the text family.

Errors

Microsoft's page documents no error conditions for TRIM beyond the ordinary ones (see Sources). In practice the reachable errors are:

The Handbook has not established which argument kinds fall in that second row for TRIM specifically.

How to implement

Notes for implementers

• The character set is the specification. Implement TRIM against an explicit list of code points, not against a library's is_whitespace predicate. A Unicode-aware is_whitespace matches tab, newline, non-breaking space, en quad, ideographic space and more; the evidence recorded upstream says the non-breaking space at least survives Excel's TRIM. Using the language's default whitespace class here is the single most likely way to diverge.
• Collapse, don't just strip. A port that reuses a host-language trim() produces wrong interior results for every string with a double space.
• Tabs and newlines are a separate question from the non-breaking space, and this Handbook does not have an answer for them. Do not assume they behave like the space just because the language you are porting from groups them together.
• The 32,767-code-unit text cap applies to the result as to any string; see the value universe. TRIM only ever shortens, so it cannot create a cap violation, but it can be the step that makes an over-long input fit.
• Lift a single array-valued argument rather than rejecting arrays outright — that is the behaviour the upstream record pins.

Rust code

16 trim_fn "TRIM" (1, 1) [V "text"] |s, a, sv| {
    let text = text_arg(s, a, 0)?;
    Ok(CalcValue::text(k::trim_ascii_spaces(&text)))
}

pub(crate) fn trim_ascii_spaces(text: &ExcelText) -> ExcelText {
    let mut out = Vec::new();
    let mut pending_space = false;
    let mut started = false;
    for unit in text.utf16_code_units() {
        if *unit == 32 {
            if started {
                pending_space = true;
            }
            continue;
        }
        if pending_space && !out.is_empty() {
            out.push(32);
        }
        out.push(*unit);
        started = true;
        pending_space = false;
    }
    ExcelText::from_utf16_code_units(out)
}

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

• CLEAN — the neighbouring "tidy the text" function, and the one TRIM is most often confused with. CLEAN removes non-printable control characters; TRIM normalises spaces. Neither does the other's job, and TRIM(CLEAN(x)) is the common pairing.
• SUBSTITUTE — removes or replaces a specified character. This is the escape hatch for whitespace TRIM will not touch, notably UNICHAR(160).
• TEXTSPLIT with ignore_empty performs the same "drop empty pieces" operation on a general delimiter, which is TRIM's rule in generalised form.
• TEXTJOIN with ignore_empty set is the rejoining half of the same idea.
• TRIMRANGE and the trim-reference operators are not related despite the name: they trim empty rows and columns from a reference, not spaces from a string.
• EXACT and = comparisons are where untrimmed data usually announces itself, which is why TRIM shows up so often in lookup keys.

Named variants

No named variants are recorded yet.