Contact
12 programs
Explore the site

CLEAN Function (Gnumeric)

Text Beginner Gnumeric

Learn the CLEAN function in Gnumeric: Removes character codes below32 from text in Gnumeric 1.12.62.

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

Validation limits: Examples were independently checked against character encodings and the checksum-verified official Gnumeric 1.12.62 source. No native Gnumeric execution or screenshots were performed.

Purpose: clean imported data without erasing ordinary characters

CLEAN removes non-printable characters from a string, leaving regular characters and whitespace, according to Gnumeric’s own String manual [R1]. It is useful when text copied from a scanner, export, or external report contains an invisible bell/control character, which can prevent text matching or create unexpected labels.

This is different from TRIM: TRIM reduces runs of spaces between words; Gnumeric describes CLEAN as filtering non-printable characters, not as normalizing every space or all Unicode whitespace [R1]. The verified 1.12.62 source keeps code points >=32 and discards those below32. Thus ASCII TAB/LF/CR are removed by that callback, while U+00A0 and higher Unicode formatting characters are retained. This is a source-derived prediction, not a native worksheet observation; CLEAN is not a universal Unicode nonprinting-character filter.

Official syntax, argument types, and locale

=CLEAN(text)
Position Argument Required? Type / default
1 text Yes Text string or cell reference containing text; no optional arguments or defaults.

The Gnumeric manual specifies exactly CLEAN(text) with one string argument [R1]. The formula itself uses no argument separator; examples that compose multiple functions use the English syntax from Gnumeric’s documentation. Some locales substitute other list separators and may localize names. This text operation is not a number formatter, and locale-sensitive decimal parsing is not its purpose.

The public 1.12 catalogue marks clean Complete with Basic project testing; release announcement confirms 1.12.62 [R2, R3]. A catalogue status is not an on-machine test, nor confirmation that any particular installation/platform has executed these cases.

Examples with exact expected output

CHAR(7) creates character code 7 from the documented CP1252 mapping; Gnumeric’s upstream CLEAN help itself uses a CHAR(7) example [R1, R4]. For visible output, formulas in the table concatenate a hidden code between printed letters.

Enter this formula Expected result Evidence / check
=CLEAN("Gnumeric") Gnumeric Already-printable input unchanged; documentation-based expectation [R1].
=CLEAN("Gnumeric"&CHAR(7)) Gnumeric Nonprinting code 7 removed; documented Gnumeric illustration [R1, R4].
=CLEAN("A"&CHAR(7)&"B") AB One embedded control code filtered; independently reasoned [R1, R4].
=CLEAN("A B") A B Regular space preserved under documented wording [R1].
=LEN(CLEAN("A"&CHAR(7)&"B")) 2 Result has two visible characters; independent calculation and LEN manual [R1].
=CLEAN("") "" Empty text remains empty (no characters to remove); independent reasoning [R1].

All shown strings use worksheet double quotes. The expected "" is the empty string, not two quotation marks shown in the cell. No screenshot or measured Gnumeric output is supplied.

Copy-and-test worksheet: labels with an invisible control

Cell Enter Expected result / role
A1 Raw label Heading
B1 Cleaned label Heading
C1 Character count Heading
A2 ="Lot"&CHAR(7)&"-A" Contains a hidden control between Lot and -A
A3 ="Lot-B" Printable comparison row
B2 =CLEAN(A2) Lot-A expected
B3 =CLEAN(A3) Lot-B expected
C2 =LEN(B2) 5 expected
C3 =LEN(B3) 5 expected

You can copy B2 and B3 into a report after confirming them in Gnumeric. The distinctive hidden character in A2 is generated by a formula, so you can reproduce the case without downloading any external file. If a downstream lookup still fails, do not assume CLEAN is sufficient: inspect space, case, and visually similar Unicode characters separately.

Advanced pattern: combine non-printable removal with spacing cleanup

For an import where both control characters and unwanted repeated spaces are possible, use two distinct Gnumeric-documented functions:

=TRIM(CLEAN(A2))

CLEAN acts on non-printable characters, then TRIM normalizes ordinary spaces as its own manual describes [R1]. With A2 set to =" Lot"&CHAR(7)&" A ", the documentation-based expected display is Lot A; check the actual TRIM treatment in the installed Gnumeric version before using this result operationally. The nested formula uses only one argument per function and does not require a list separator.

Errors, limits and troubleshooting

  • Nothing changed: The original text may consist only of printable characters and ordinary spaces. CLEAN is not TRIM; ordinary spaces can be preserved [R1].
  • The value looks blank but is not matching: Generate a controlled CHAR(7) test above. For unexplained Unicode control/format characters or nonbreaking spaces, native verification required; do not assert they are all removed [R1, R4].
  • A line break remains or disappears unexpectedly: The manual’s broad whitespace wording is imprecise: the verified 1.12.62 callback discards LF(10), CR(13) and TAB(9) because each is below32. This is source-derived behavior; native display remains untested [R1].
  • There is an error in the input cell: The documentation does not specify a detailed error-propagation table here; test Gnumeric’s behavior with an error-valued reference before relying on a detailed error table [R1].
  • I passed several arguments: CLEAN(text) has exactly one documented argument. Combine text outside the call with & or use a cell reference [R1].
  • I expected case conversion or accent stripping: CLEAN is not a transliteration or lowercase function. Use appropriately documented functions for a different transformation; accent removal is not promised [R1].
  • I expected Excel to behave identically on every imported file: Gnumeric marks the function as Excel compatible [R1], but differences in encodings and character categories are outside the claims proven by this guide.

Gnumeric’s own manual labels CLEAN Microsoft Excel compatible [R1]. This is documented function compatibility; no specific Windows Excel or LibreOffice run was performed. In particular, the guide does not copy another spreadsheet’s nonprinting-character table and claim it for Gnumeric.

Related Gnumeric guides: CHAR (generate control codes) · TRIM (spaces) · LEN (character count) · CODE (CP1252 codes) · T (retain text).

Primary source verification: The official Gnumeric 1.12.62 archive was verified against GNOME’s SHA-256 checksum, then plugins/fn-string/functions.c was inspected without compilation or execution.

Official evidence and validation labels

Validation: Official manual and checksum-verified release-source inspection; independent code-point, encoding and string calculations. Native Gnumeric execution, screenshots, locale/platform and workbook round-trip tests were not performed.