Contact
12 programs
Explore the site

CLEAN Function (LibreOffice Calc)

Text Beginner LibreOffice Calc Introduction not established

The CLEAN function in LibreOffice Calc removes non-printable characters from text, including control codes and hidden characters often found in imported or pasted data. It is essential for text normalization and data cleaning.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The **CLEAN** function removes non-printable characters from text in LibreOffice Calc. These characters often appear in imported data, copy-pasted content, or text generated by external systems. CLEAN is essential for normalization, data cleaning, and preparing text for parsing with functions like FIND, SEARCH, LEFT, RIGHT, and MID.

What the CLEAN Function Does

  • Removes non-printable ASCII characters (0–31)
  • Removes control codes (line breaks, tabs, etc.)
  • Cleans imported or pasted text
  • Prepares text for parsing and comparison
  • Works with text, numbers (converted to text), and formulas

It is designed to be robust, predictable, and essential for data hygiene.

Syntax

CLEAN(text)

Arguments

  • text:
    The text string to clean.

Basic Examples

Remove control characters

=CLEAN(A1)

Remove line breaks

=CLEAN("Hello" & CHAR(10) & "World")

Returns "HelloWorld".

Remove tabs

=CLEAN("A" & CHAR(9) & "B")

Returns "AB".

Clean imported CSV/TSV data

=CLEAN(A1)

Clean text before TRIM

=TRIM(CLEAN(A1))

Advanced Examples

Remove non-printable characters before parsing

=FIND(" "; CLEAN(A1))

Clean text before concatenation

=CONCAT(CLEAN(A1); " "; CLEAN(B1))

Clean text before comparison

=IF(CLEAN(A1)=CLEAN(B1); "Match"; "No match")

Clean text before VALUE conversion

=VALUE(CLEAN(A1))

Clean text before SUBSTITUTE

=SUBSTITUTE(CLEAN(A1); " "; "_")

Clean text before TEXTJOIN

=TEXTJOIN(" "; TRUE; CLEAN(A1:A5))

Remove hidden characters from API or system output

=CLEAN(A1)

Clean text containing mixed whitespace

=TRIM(CLEAN(SUBSTITUTE(A1; CHAR(160); " ")))

(Handles non-breaking spaces too.)

Edge Cases and Behavior Details

CLEAN removes:

  • ASCII control characters (0–31)
  • Tabs (CHAR(9))
  • Line breaks (CHAR(10), CHAR(13))
  • Hidden formatting characters

CLEAN does not remove:

  • Regular spaces
  • Multiple spaces
  • Non-breaking spaces (CHAR(160))
  • Unicode whitespace
  • Printable characters

Use TRIM or SUBSTITUTE for those.

CLEAN of an empty string returns empty string

=CLEAN("") → ""

CLEAN of a blank cell returns empty string

=CLEAN(A1) → ""

CLEAN of a number converts it to text

=CLEAN(123) → "123"

CLEAN of an error propagates the error

=CLEAN(#N/A) → #N/A

CLEAN does not collapse spaces

Use TRIM for that.

CLEAN does not remove Unicode control characters outside ASCII 0–31

Use SUBSTITUTE if needed.

Common Errors and Fixes

CLEAN does not remove all unwanted characters

Cause:

  • Non-breaking spaces
  • Unicode whitespace
  • Printable but unwanted characters

Fix:
Use SUBSTITUTE(A1; CHAR(160); " “) or TRIM.

CLEAN returns unexpected text

Cause:

  • Control characters removed
  • Line breaks collapsed
  • Tabs removed

CLEAN converts numbers to text

Cause:

  • CLEAN always returns text
  • Use VALUE(CLEAN(A1)) to restore numeric type

Best Practices

  • Use CLEAN before TRIM for full normalization
  • Use CLEAN on imported or pasted data
  • Use CLEAN before parsing with FIND/SEARCH
  • Use CLEAN before concatenation to avoid hidden characters
  • Use SUBSTITUTE to remove non-breaking spaces
  • Combine CLEAN + TRIM + SUBSTITUTE for bulletproof cleaning
CLEAN is your hidden‑character scrubber — perfect for removing control codes, line breaks, tabs, and invisible junk that breaks formulas and parsing.
  • Use TRIM to remove extra spaces
  • Use SUBSTITUTE to remove specific characters
  • Use LEFT, RIGHT, MID for extraction
  • Use VALUE to convert cleaned text to numbers
  • Use TEXTJOIN and CONCAT for assembly

By mastering CLEAN and its companion functions, you can build clean, reliable, and fully normalized text‑processing workflows in LibreOffice Calc.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=CLEAN("A"&CHAR(10)&"B")

Expected result: AB.

Remove a control character such as a line break.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.