The CLEAN function in Excel is a handy text function that removes non-printable characters or line breaks from a dataset. Let’s explore some examples of how to use it:
Removing Non-Printable Characters: Suppose you have email IDs with non-printable characters (e.g., CHAR(15), CHAR(12)). To get the real email IDs, use this formula:
=CLEAN(C5)Here, C5 represents the imported email ID. Drag down the fill handle to apply the formula to other cells.
Removing Line Breaks: If you want to remove line breaks from student names, use the same CLEAN function:
=CLEAN(C5)Replace C5 with the student name cell reference.
Removing Extra Spaces: Sometimes, you may have extra spaces (CHAR(32)) in text strings. Combine CLEAN with TRIM to remove non-printable characters and extra spaces:
=TRIM(CLEAN(C5))Again, replace C5 with the relevant cell reference.
Remember that the CLEAN function only removes non-printable characters represented by numbers 0 to 31 in the 7-bit ASCII code. Happy cleaning!
