Search This Blog

pCloud Crypto

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! 

pCloud Lifetime

The customer care and complaint management contact details for BASIC Bank Limited

If you need to get in touch with  BASIC Bank Limited  in Bangladesh regarding complaints or suggestions, here are the ways you can reach the...