CLEAN

What it does

CLEAN(text)

CLEAN returns its argument with every character in a fixed set deleted. It is a filter, not a replacer: removed characters leave nothing behind, so the result is never longer than the input and carries no substitute spaces.

The set is where the whole content of this function sits, and Microsoft's article is unusually explicit about its history. CLEAN was designed to remove "the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31)". The article then states the limitation in its own voice: in the Unicode character set there are additional nonprinting characters — it names values 127, 129, 141, 143, 144 and 157 — and CLEAN does not, by itself, remove them.

Filtering is defined over UTF-16 code units (see the value universe), which matters only in that a surrogate pair can never be hit by a set of values below 158, so astral characters survive CLEAN intact.

Arguments

text — required. Microsoft's article describes it as any worksheet information from which you want to remove nonprintable characters. It is a text argument, so a number or logical converts to text first and comes back as its rendering: CLEAN of a number is text, not a number.

There is no second argument. The removal set is not configurable, which is why the documented workaround for the remaining Unicode nonprinting characters is to compose SUBSTITUTE calls around CLEAN rather than to parameterise it.

Try it

Quirks

Result and edge cases

The return kind is Text. Notable boundaries:

• Empty input yields empty text — there is nothing to filter and nothing to add.
• All-nonprinting input yields empty text, not an error. This is the case worth remembering, because a cell holding only control characters is indistinguishable after CLEAN from an empty cell in every downstream test except ISBLANK.
• Whitespace is not touched. Space (32) is outside the removal range; tab (9) and the line-break characters (10, 13) are inside it. CLEAN therefore joins lines without inserting a separator — "a" & CHAR(10) & "b" cleans to ab, not a b. This is the single most common surprise, and it is the reason CLEAN and TRIM are almost always used together.
• Arrays, empty cells and omitted arguments follow the shared call model (chapter 03).

Errors

The article does not enumerate error conditions for CLEAN, and none of the ordinary text inputs suggests one: the function has no domain to violate. The reachable errors are the shared ones — an error value arriving as the argument propagates (chapter 02), and a value kind with no text conversion surfaces #VALUE! under the shared coercion rules rather than under any rule of this function's own.

How to implement

Notes for implementers

1. The removal set is the specification. Write it as data, not as a range test, so it can be diffed against evidence when evidence exists. The two candidate sets differ only at six values, and a range test hides that.
2. Filter, do not replace. Indices into the input are invalidated by CLEAN, so any code that cleans a string and then applies a previously computed FIND position is wrong.
3. Operate on code units, not on decoded characters. Decoding first is harmless for correctness here but forces a decision about ill-formed UTF-16, which the value-universe chapter records as a reachable state in Excel text.

Rust code

17 clean_fn "CLEAN" (1, 1) [V "text"] |s, a, sv| {
    let text = text_arg(s, a, 0)?;
    Ok(CalcValue::text(k::clean(&text)))
}

pub(crate) fn clean(text: &ExcelText) -> ExcelText {
    let filtered: Vec<u16> = text
        .utf16_code_units()
        .iter()
        .copied()
        .filter(|&u| !(u < 32 || matches!(u, 129 | 141 | 143 | 144 | 157)))
        .collect();
    ExcelText::from_utf16_code_units(filtered)
}

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

• TRIM is the whitespace sibling: it removes leading and trailing spaces and collapses runs of interior spaces. CLEAN and TRIM partition the usual import-cleanup job between them, and neither does the other's work.
• SUBSTITUTE is the escape hatch for the characters CLEAN documents itself as leaving behind (127, 129, 141, 143, 144, 157), and for the non-breaking space (160), which is not in CLEAN's set and is not a space to TRIM either. Import-cleanup formulas in the wild are usually TRIM(CLEAN(SUBSTITUTE(x, CHAR(160), " "))) for exactly this reason.
• ASC and DBCS are the other width- and encoding-normalising text functions; they change characters rather than delete them.

Named variants

No named variants are recorded yet.