How to use Excel CLEAN Function

Introduction

CLEAN in excel is a text/string function. This function cleans non printable elements and line breaks in a text. It can be used to omit non printable elements from text that has been imported from other software or online links, which is not usable in your operating system. For example, ($week©) can be cleaned by using =CLEAN($week©) formula which gives (week) as the result.

Syntax

=CLEAN(Text)

Arguments

Text: It is the text which will be cleaned, cell number which contains text can also be used.

Keynotes

CLEAN function can omit first 32 characters from ASCII character system.
CLEAN function and TRIM function are different in such a way that TRIM function omits spaces of ASCII code 32.
This function was added in Excel 2000 and is available in later versions also.

Examples

In this example, CLEAN function is used to omit non printable material present in column A and retrieve corrected values in column B as follows:

When CLEAN function is used with CHAR(2)&”hello”&CHAR(8), it gives hello as the result. And similarly other values are cleaned.

CLEAN_FUNCTION EXAMPLE FILE Download