CLEAN Function in Google Sheets: Examples, Pitfalls & Compatibility

=CLEAN(text)

text (required): The text or one cell containing the characters to inspect.

Examples use English function names, comma argument separators and decimal points. Adapt formula separators to your workbook settings, not punctuation inside quoted text.

Use CLEAN when imported descriptions contain tabs or line breaks that should disappear. Do not use it as a general spelling, whitespace or Unicode repair tool. Removing a separator is a different decision from replacing that separator with a space.

Each example is independent. Where input cells are listed, enter those values first; otherwise the formula is self-contained. Expected outputs are independently checked calculations or documented error expectations, not captured results from Google Sheets.

Example 1: Remove a line feed from an imported label

=CLEAN("Stock"&CHAR(10)&"room")

Expected result: "Stockroom" (quoted here to make spaces and text boundaries visible).

Deleting the line feed joins the fragments. This is useful only when the joined result is the intended label.

Example 2: Measure the cleaned identifier

=LEN(CLEAN("X"&CHAR(9)&"Y"))

Expected result: 2.

The result has two visible characters. LEN makes the removal measurable rather than relying on what the cell displays.

Example 3: Normalize a deliberately spaced label

=TRIM(CLEAN("  blue "&CHAR(10)&" box  "))

Expected result: "blue box" (quoted here to make spaces and text boundaries visible).

After the line feed is removed there are two ordinary spaces between the words; TRIM reduces that run to one.

Keep an untouched input column and place the cleaned result beside it. Compare LEN on the original and result to see whether any characters were removed. Review a few changed rows before pasting values over the source. A shorter string is not automatically a better identifier: deleting the only separator between two words can accidentally create a new word or code.

Words become joined. CLEAN removes the control character; it does not insert a replacement space. Where a tab separates two fields, use SUBSTITUTE to replace that known tab with a space before trimming.

Visible spaces remain. Ordinary spaces are not the ASCII controls this function targets. Apply TRIM only when collapsing repeated ordinary spaces is appropriate.

Two cleaned keys still differ. A nonbreaking space or another Unicode character may remain. Inspect the character code rather than repeatedly applying CLEAN.

Google explicitly limits CLEAN to nonprintable ASCII characters and warns that non-ASCII Unicode controls are not removed. Sheets may hide the troublesome control in the display even while it remains in the stored text. See the vendor reference for the current definition.

Both help pages document the ASCII-cleaning purpose. This does not make CLEAN a Unicode normalizer in either application. When exchanging identifier columns, compare the resulting strings rather than judging only their appearance.

Compare the CLEAN guide for Microsoft Excel and its official documentation. Having a guide in both libraries establishes two documented references, not a certificate of identical behavior across releases, locales or file conversions.

TRIM, SUBSTITUTE, LEN, CODE · All Google Sheets functions.

Editorial verification: Official references retrieved on 2026-10-12. The three worked cases are included in this release’s reproducible Python checking suite. It checks the stated arithmetic, calendar or text model and the expected documented error classification; it is not the Google Sheets calculation engine. No native application run, UI screenshot, maximum-size benchmark or cross-version certification is claimed. Review the editorial testing standards before relying on a formula in a critical workbook.