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.

Leave a Reply

Close Menu