30 Excel Functions in 30 Days: 29 – CLEAN

Icon30DayYesterday, in the 30XL30D challenge, we jumped around a workbook, and opened Excel files and websites, by using the HYPERLINK function.

For day 29 in the challenge, we’ll examine the CLEAN function. Sometimes the data you get from a website, or in a download file, has some unwanted characters, and the CLEAN function can help you fix it. It doesn’t do much heavy lifting though, and refuses to help with the mess that the kids make. This will be perfect for a lazy Sunday!

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the CLEAN information and examples, and if you have other tips or examples, please share them in the comments.

Function 29: CLEAN

The CLEAN function shows removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157.

Clean00

How Could You Use CLEAN?

The CLEAN function can remove some non-printing characters from text , but not all of them. You can use CLEAN, or other functions when necessary, to:

  • Remove some non-printing characters
  • Replace non-printing characters in text

CLEAN Syntax

The CLEAN function has the following syntax:

  • CLEAN(text)
    • text is any information from which you want the non-printing characters removed

CLEAN Traps

The CLEAN function only removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157. For other non-printing characters, such as the non-breaking space character 160, you can use SUBSTITUTE to replace them with space characters, or empty strings.

Example 1: Remove non-printing characters

The CLEAN function works to remove some non-printing characters, such as those in the 0-30 range of the ASCII character set. In this example, I added characters 9 and 13 to the original text string from C3.

=CHAR(9) & C3 & CHAR(13)

The LEN function shows that the number of characters in cell C5 increased to 15, with those non-printing characters included.

Clean01a

With the CLEAN function, in cell C7, those characters are removed, and the number of characters is reduced by 2, so it’s back to the original 13 characters.

=CLEAN(C5)

Clean01b

Example 2: Replace non-printing characters

For the characters that the CLEAN function can’t remove, like characters 127 and 160, you can use the SUBSTITUTE function to replace them.

=SUBSTITUTE(E3,CHAR(C3),””)

Clean02b

Download the CLEAN Function File

To see the formulas used in today’s examples, you can download the CLEAN function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the CLEAN Video

To see a demonstration of the examples in the CLEAN function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Excel CLEAN Function Removes Non-Printing Characters


_____________

You may also like...

8 Responses

  1. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] 30 Excel Functions in 30 Days: 29 – CLEAN […]

  2. Gregory says:

    The character coding is different on a Mac so CLEAN only works for CHAR values from 1-31. The only other non-printing character is 127.

  3. Thanks Gregory, that’s good to know! It’s been years since I used a Mac.

  4. Ron says:

    Hi Debra

    I had a need for something a little stronger than clean and did a worksheet function that removed any character that wasn’t in the set of a-z, A-Z or 0-9. It did a loop by character in the cell contents and built an output string with only the required characters. It saved working out what characters had been included in the data.

    Ron

    Function HarshTrim(strCode As String) As String
    ‘ Created 11/09/2012
    ‘ Ron
    ‘ Usage: Copy this code/module into the worksheet you wish to use the function in
    ‘ In the worksheet use in syntax HarshTrim()
    ‘ Example: harshTrim(A2)
    ‘ The result will be the contents of the reference cell with all characters stripped out except 0-9, A-Z and a-z

    Dim output As String
    Dim i As Integer
    Dim S() As Byte

    S = StrConv(strCode, vbFromUnicode)
    output = “”
    For i = 0 To UBound(S)
    If (S(i) >= 48 And S(i) = 65 And S(i) = 97 And S(i) <= 122) Then
    output = output & Chr(S(i))
    End If
    Next
    HarshTrim = output
    End Function

  5. Mike says:

    @Ron
    Hi,

    I believe the line with codes ranges should look rather like that:
    If (S(i) >= 48 And S(i) = 65 And S(i) = 97 And S(i) <= 122) Then

    Best!
    Mike

  6. Chheng You says:

    Dear Sir or Madam,

    please kindly help me as below:

    when we sum the amount like “1,000”,
    and i want to show the detail as:
    “One thousand only” like this in Excel.

  7. Respected sir or mam,
    we are not understand the clean function so please give any more information about clean and acosh function. plz replay as early as possible.

Leave a Reply

Your email address will not be published. Required fields are marked *