Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

CLEAN

Removes non-printable characters from a string in Excel
Removes all non-printable characters from Text

Rational

Removes non-printable characters from a string.

Description

The Excel CLEAN function deletes the non-printable characters that are most often the codes from 1 to 31 of the ANSI character set. As the function name suggests, it cleans all the “garbage” characters that often appear after importing texts.

Syntax

= CLEAN (text)

Parameters

text – Text character to clean.

Return Value

Delete non-printable characters.

Key Notes

  • The Excel CLEAN function is used to remove non-printable characters or line breaks from a string.
  • The Excel removes ANSI character from 1 to 31, other non-printable characters after ANSI 31 does not eliminate. For Example, if you want to clean the exclamation mark in each text “Go Away!”, ‘=”Go Away”& CHAR(33)’, the CLEAN function will return “Go Away!” (no change).

The Microsoft Excel CLEAN function returns the number based on the ASCII value.
Return numbers depend upon the operating environment used by computer:

  1. For Windows
  2. For MAC

 

Keep Reading Similar Functions:

Return the corresponding ANSI character of a number in Excel

CHAR

The CHAR function display characters based on the underlying ANSI numeric codes. I.e. in Window, = CHAR(33) in Excel returns “!” (exclamation mark).

Read More »
corresponding code of a character in Excel

CODE

The CODE function displays a unique number of each valid character based on the underlying set standard of ANSI. I.e, =CODE(B8) returns ! (exclamation mark).

Read More »
Remove unnecessary spaces from text in Excel

TRIM

The TRIM function removes excess spaces from a text string except for one space between each word. E.g. =TRIM(“Hello World”) [3 spaces] return “Hello World”.

Read More »