CLEAN formula in Excel with examples

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:

1. 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.

2. 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.

3. 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!