MY CARTVideo Lesson:
This video lesson, titled “How to Use the Clean Function to Remove Hidden Characters in Excel,” shows you how to use the CLEAN function to remove hidden characters in Excel.
How to Use the CLEAN Function to Remove Hidden Characters in Excel:
The CLEAN function in Excel removes non-printing characters from cells in Excel. The non-printing characters the CLEAN function removes are the first 32 nonprinting characters in the 7-bit ASCII code. This function helps to ensure you don’t send hidden data, like carriage returns, when exporting Excel data file to a different file format like CSV, for example.
In a CSV file, a carriage return indicates the end of a record. If you accidentally leave a non-printing character like a carriage return within the middle of a record when you export it from Excel, it often breaks the record in the CSV when it is imported later by separating it into two records at the extra carriage return. One way to avoid this is to use the CLEAN function on cells you think may contain hidden or non-printing characters in Excel before you export the file.
To use the CLEAN function in Excel, first click into a cell adjacent to the cell to clean and where you want the clean results to appear. Then type “= CLEAN(“ into the cell. Then either type or select the cell address of the adjacent cell to clean. Finally, type the closing parenthesis and press the “Enter” key on your keyboard.
You can then copy the cell to any adjacent cells in the column, as needed. Lastly, you can then copy the clean results and use the “Paste Special” and “Values” selection to paste the clean values into the desired location in the data. Then delete the unclean data, as needed. You can then safely export the results to CSV, if needed.
Other Uses of the CLEAN Function and Important Notes:
Users also use the CLEAN function in Excel to remove extraneous hidden programming characters that may appear in an Excel worksheet if importing data files containing low-level computer code. As Microsoft notes on its help page for the CLEAN function in Excel, it’s important to know that this function doesn’t remove the nonprinting Unicode characters of values 127, 129, 141, 143, 144, or 157.
