CLEAN function

The CLEAN function in Excel is a simple but useful text function that removes non-printable characters from a text string. These non-printable characters can often be hidden and cause issues when working with data, so CLEAN helps clean up your data.

Syntax

CLEAN(text)

Arguments

text	The text from which you want to remove non-printable characters. This is the only required argument.

How to use

The CLEAN function is straightforward to use. You provide a text string as an argument, and it returns a new text string with non-printable characters removed. Here’s a simple example:

This formula will remove the non-printable character (represented by ) from the text and return “Hello, World!”

You can use the CLEAN function to clean up text from various sources, such as imported data, web scraping, or data entry, where non-printable characters may be present. It ensures your data is in a clean, readable format for further analysis or presentation.

It’s essential to remember that the CLEAN function is not designed to handle all text-cleaning scenarios, but it is a useful tool for dealing with non-printable characters.