CLEAN Function (LibreOffice Calc)

• 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.

CLEAN(text)

Arguments

• text:
  The text string to clean.

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))

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.)

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.

• 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

• 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.