The CLEAN function accepts just one argument, text, which can be a text string or number. CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), if any are found, and returns the result. Text without these characters is returned unchanged. Note that CLEAN will remove line breaks if found. CLEAN will not remove extra space characters. To remove extra space, use the TRIM function. You can use CLEAN and TRIM together in one formula like this:
ASCII limitation
The CLEAN function removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from text. Unicode contains other non-printable characters that are not removed. To remove specific characters beyond the first 32 ASCII characters, you can use the SUBSTITUTE function with the CHAR function. For example to remove character 202: You can use the CODE function to determine the number for a problematic character. See this page for more information.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
