CLEAN

The CLEAN function removes all non-printable characters from a text.

This function helps clean up data and make it more suitable for analysis.

Syntax

CLEAN(text)

Argument	Description	Permitted values
text	String in which non-printable characters need to be removed	Text string or reference to a cell containing text

Examples of use

Clean up the text

=CLEAN("Hello! This is text with non-printable characters: ” & CHAR(10) & CHAR(13))

This formula will remove characters that may not be visible, such as line breaks (CHAR(10) and CHAR(13)), and return the cleaned text.

Cleaning up data from a cell

=CLEAN(A1)

If cell A1 contains text with non-printable characters, this formula will return the text without them.

Notes

The CLEAN function only removes non-printable characters from the ASCII encoding standard.