Purpose
Return value
Syntax
=CLEAN(text)
- text - The text to clean.
Using the CLEAN function
The CLEAN function accepts a text string and returns text that has been “cleaned” of line breaks and other non-printable characters. You can use CLEAN to strip non-printing characters and strip line breaks from text. For example, to clean text in cell A1:
=CLEAN(A1) // clean text in A1
The CLEAN function accepts just one argument, text, which can be a text string or number. CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), if any are found, and returns the result. Text without these characters is returned unchanged. Note that CLEAN will remove line breaks if found.
CLEAN will not remove extra space characters. To remove extra space, use the TRIM function . You can use CLEAN and TRIM together in one formula like this:
=TRIM(CLEAN(A1)) // clean and remove extra space
ASCII limitation
The CLEAN function removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from text. Unicode contains other non-printable characters that are not removed. To remove specific characters beyond the first 32 ASCII characters, you can use the SUBSTITUTE function with the CHAR function . For example to remove character 202:
=SUBSTITUTE(A1,CHAR(202),"") // remove character 202
You can use the CODE function to determine the number for a problematic character, then use that number inside CHAR to return the character in a formula. See this page for more information.
Purpose
Return value
Syntax
=CODE(text)
- text - The text for which you want a numeric code.
Using the CODE function
The CODE function returns a numeric code for a given character. For example, CODE(“a”) returns the code 97:
=CODE("a") // returns 97
With the character “a” in cell A1, the formula below returns the same result:
=CODE(A1) // returns 97
The CODE function takes a single argument , text , which is normally a text value. If text contains more than one character, the CODE function returns a numeric code for the first character:
=CODE("A") // returns 65
=CODE("Apple") // returns 65
The CODE function will handle numeric input for the numbers 0-9:
=CODE(1) // returns 49
Generally speaking, the number returned by CODE is the code for a character in ASCII decimal notation. The CODE function was designed to operate in an ASCII /ANSI world, and only understands how to map characters that correspond to numbers 0-255. For extended character support on modern Unicode systems, see the UNICODE function .
Reverse CODE
To get a character for a given numeric code, you can use the CHAR function:
=CHAR(65) // returns "A"
CHAR performs the reverse of CODE, taking a numeric code and returning the corresponding character.
