The Excel CLEAN function removes non-printable characters from a string of text. This function is useful to clean imported data from a database or system that may contain non-printable characters. It can be used as a formula on a worksheet in an Excel workbook.
The Excel CLEAN Function
- text = string of characters that need non-printable characters removed
Please note, the CLEAN function does not remove all non-printable characters from a string. There are some Unicode characters (127, 129, 141, 143, 144 and 157) that this function will not clean.
So, depending on what non-printable characters are in your data, you may need to use further methods to clean the data you’ve imported. You’ll need to use the SUBSTITUTE function combined with the CLEAN function to clean them.
The cell A1 has the formula “=CHAR(1)&”string”&CHAR(4)”. The CHAR(1) and CHAR(4) are non-printable characters that will be cleaned from the string. Note how result of the formula of “=CLEAN(A1)” in cell C1 shows just the printable text of “string” in the cell.