Jump to content

Documentation/Calc Functions/CLEAN

From The Document Foundation Wiki


Function name:

CLEAN

Category:

Text

Summary:

Removes non-printable characters from the supplied text string.

Syntax:

CLEAN(Text)

Returns:

Returns a text string that corresponds to the argument string, after removal of non-printable characters.

Arguments:

Text is a text string (in quotation marks), or a reference to a cell containing such a text string, which is the string to be "cleaned".

Additional details:

CLEAN removes a specific set of non-printable characters that are defined in the C0 Controls and Basic Latin Unicode block. The exact non-printable characters that will be removed by CLEAN are:

  • Any character in the code point range U+0000 to U+001F (inclusive).
  • The character with code point U+007F.

Space characters (code point U+0020) are not removed by CLEAN.

CLEAN does not currently remove any non-printable characters defined in other Unicode code blocks and thus is not totally compliant with ODF 1.2.

Examples:

Formula Description Returns
=LEN(D1) where cell D1 contains the formula =CHAR(7) & "cat" & CHAR(8) Here the LEN function indicates that the string in cell D1 contains five characters, although only three are visible. 5
=LEN(CLEAN(D1)) where cell D1 contains the formula =CHAR(7) & "cat" & CHAR(8) Here the LEN function indicates that the "cleaned" version of the string in cell D1 contains only three characters. CLEAN has removed the non-printable bell (U+0007) and backspace (U+0008) characters. 3

Related LibreOffice functions:

None.

ODF standard:

Section 6.20.4, part 2

Related (or similar) Excel functions:

CLEAN