Contact
12 programs
Explore the site

CLEAN Function in Apple Numbers: Syntax, Examples, and Troubleshooting

Text Beginner Apple Numbers

Remove common invisible control characters from imported text in Apple Numbers without losing ordinary spaces.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Apple Numbers. How formula examples are checked

Quick answer: =CLEAN(A2) removes the common nonprinting characters with Unicode codes 0–31 from text stored in A2. It does not remove every invisible Unicode character, nor does it normalize ordinary extra spaces. Apple’s CLEAN help explicitly documents codes 127, 129, 141, 143, 144, and 157 as nonprinting cases that CLEAN does not remove by itself.

Why use CLEAN instead of TRIM?

Use CLEAN when copied or imported text contains invisible low-level controls that interfere with text matching, lookup keys, visual display, or downstream export. The task is control-character removal, not grammar cleanup or translation. A row that visually reads Customer A might contain a hidden line break or tab from a data export.

The distinction matters:

  • CLEAN: Apple’s documented target is codes 0–31 (most common nonprinting characters).
  • TRIM: Apple’s CLEAN page points to TRIM for ordinary extra spaces. That is a separate text-normalization step.
  • SUBSTITUTE: Apple’s CLEAN page points to SUBSTITUTE for targeted replacement of certain other nonprinting characters. Do not assume CLEAN alone removes them.

Exact official syntax and argument type

=CLEAN(text)

Argument: text

Required?
Yes

Apple’s accepted type
Any value, per Apple

Effect
Removes documented common nonprinting codes from the text representation.

Argument: Additional arguments

Required?
Not documented

Apple’s accepted type
—

Effect
This is a one-argument function; there is no mode or switch.

Argument: Defaults

Required?
No optional arguments documented

Apple’s accepted type
—

Effect
Supply the intended input cell or value.

Official evidence: Apple CLEAN — Formulas and Functions Help 15.4. Apple’s wording is “most common”, not “all”; its listed exceptions matter for imported non-ASCII content.

Reproducible examples without requiring control-character typing

Create a Numbers table named Imported Text Audit. Enter the following exact literal text inputs in A, then put the indicated formula in B. The space count in quoted sample data matters. These examples can be reproduced without importing special controls.

Row: 2

A input
Report

Formula for B
=CLEAN(A2)

Expected B
Report

Why
No U+0000–U+001F characters to remove

Row: 3

A input
A B (two normal spaces)

Formula for B
=CLEAN(A3)

Expected B
A B (still two spaces)

Why
Ordinary U+0020 spaces are not in 0–31

Row: 4

A input
Item (two spaces on each side)

Formula for B
=CLEAN(A4)

Expected B
Item (spaces remain)

Why
CLEAN is not a whitespace-collapsing function

Row: 5

A input
Text?

Formula for B
=CLEAN(A5)

Expected B
Text?

Why
An ordinary printable question mark is not removed

Validation: These outputs are expected from Apple’s documented character-code rule, checked independently against the input code points. They are not observed Numbers output.

Diagnostic test with a real control character (imported text)

A control code is invisible, so a printed sample may not show what is actually stored. For an external data file in which one field contains A + U+0009 TAB + B, =CLEAN(A2) has the expected text result AB if A2 really contains those three code points in one cell. Likewise U+000A LINE FEED between A and B is in Apple’s removable 0–31 range. Importing a tab- or newline-containing file can split records/columns depending on your import workflow, so inspect A2 before asserting the test ran as intended. We have not performed such an import into Numbers and do not mislabel the expected output as a native execution observation.

Important cross-application trap: In Excel, CHAR(9) and CHAR(10) can build tabs and line breaks for CLEAN demonstrations. Apple’s CHAR requires code >=32, so =CLEAN("A"&CHAR(10)&"B") is not a valid supported Numbers reproduction. Obtain such text from actual imported data instead. See Apple CHAR.

Advanced: normalize a common nonbreaking space

A pasted U+00A0 nonbreaking space is code 160, not 0–31, so CLEAN alone does not address it under Apple’s stated rule. Apple’s CHAR permits code 160 (but the glyph looks like a space). For a value in A2, a targeted pipeline is:

=SUBSTITUTE(CLEAN(A2),CHAR(160)," ")

This first removes documented common controls and then substitutes U+00A0 with an ordinary U+0020 space. Expected A[NBSP]B becomes A B if the bracketed marker represents one actual U+00A0 character (not the literal bracket characters). A text cell with no U+00A0 is unchanged by the substitution stage. For removal of leading/repeated ordinary spaces, perform an additional TRIM step after verifying the input and desired spacing. SUBSTITUTEs and nested formulas have not been executed natively in this run; the Unicode mapping and staged transformation were checked independently.

Errors, caveats and common mistakes

Problem Explanation Corrective action
CLEAN does not remove leading spaces Space U+0020 is not a control code 0–31. Use TRIM separately if whitespace normalization is wanted.
The text still contains strange characters Apple explicitly lists unremoved codes 127, 129, 141, 143, 144, 157. Identify the actual code first; use targeted substitution after evidence.
CHAR(10) fails in a CLEAN tutorial copied from Excel Apple’s CHAR minimum permitted code is 32. Do not rely on Excel-style control-character fixtures in Apple Numbers.
Data appears modified after import Tabs and line feeds may have been interpreted by the importer as delimiters or row breaks. Verify that the raw control code is actually inside a single cell.
Ordinary punctuation vanishes unexpectedly CLEAN’s documented target is limited; another processing step may be at fault. Test each transformation separately on a copy.
You need to preserve line breaks CLEAN can remove U+000A line feeds, joining text. Make an explicit text-cleaning plan rather than applying CLEAN to all fields blindly.

Apple’s checked Help entry does not provide exact error-message wording for malformed inputs, so this page does not fabricate one. No native platform/edition error state was observed.

Locale, table references and application comparison

=CLEAN(A2) uses only one argument, so it works with either common argument-separator convention. Multi-argument formulas such as SUBSTITUTE require semicolons instead of commas in locales configured with a comma decimal separator, per Apple Functions overview. A2 is in the same table in these examples; cross-table and cross-sheet values may require an explicit table prefix (Apple cell-reference help).

Both Microsoft Excel CLEAN and Google Sheets CLEAN also document removal of low ASCII control characters. Microsoft explicitly notes exclusions 127, 129, 141, 143, 144, and 157; Google notes that nonprinting Unicode outside ASCII may remain. Do not infer identical handling for every imported encoding or data type from those descriptions alone. In particular, do not carry Excel’s permissive CHAR(10) into Apple Numbers as a test-data construction method.

Apple CHAR documents permitted character-code generation. Apple CODE inspects the first character of text, but it cannot inventory hidden characters that occur later within a string. Apple CLEAN itself provides the official links to TRIM and SUBSTITUTE for follow-up normalization.

Sources checked 2026-10-12: Apple CLEAN; Apple CHAR; Apple CODE; Apple Functions overview; Apple cell references; Microsoft Excel CLEAN; Google Sheets CLEAN.

  • CHAR — continue with a related Apple Numbers calculation.
  • CODE — continue with a related Apple Numbers calculation.
  • T — continue with a related Apple Numbers calculation.
  • REPT — continue with a related Apple Numbers calculation.
  • FIXED — continue with a related Apple Numbers calculation.

For a software-specific comparison: Microsoft Excel CLEAN, Google Sheets CLEAN, LibreOffice Calc CLEAN. A shared name does not establish identical argument, error or version behaviour.

How this guide was checked

The linked official function documentation was reviewed on October 12, 2026. Apple’s Formulas and Functions Help 15.4 identifies the documentation edition, not a minimum Numbers release or a guarantee of historical platform parity. Selected worked examples were checked using independent mathematical or text-processing models; results are expected values, not observations from the spreadsheet application. Native Apple Numbers execution was not performed. Undocumented coercion, exact error messages and platform-specific behaviour remain unverified.

View reproducible example inputs and independent check results. This record is not a native spreadsheet test log.