Function: CLEAN

by | Apr 18, 2019 | Excel Tips & Tricks

excel clean function

The Excel CLEAN function is a Text formula that removes "non-printable" characters from a text string. The function is especially useful for eliminating unsupported characters from a text that was imported from an external source. In this guide, we’re going to show you how to use the Excel CLEAN function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Excel CLEAN Function Syntax

CLEAN(text)

Arguments

text

The text that you want to clean.


Example

You can enter a static string or a cell reference for the CLEAN function parameter. For example,

=CLEAN("2 + 2

equals

5")

returns the string "2 + 2 equals 5" by removing all new line characters. After non printable (or visible) new line characters are removed, the text then fits into a single line.

 Download Workbook

Tips

  • Use the CLEAN function to easily remove non-printable characters on text strings that are imported from external sources, like an HTML page.
  • The Excel CLEAN function will remove non-printable 7-bit ASCII characters (values 0 through 31) from the specified text. The non-printable characters in Unicode character set (values 127, 129, 141, 143, 144, and 157) will be ignored by the formula.
  • To remove leading, trailing, or extra spaces (ASCII code 32), use the TRIM function instead.