How to use the CLEAN Function in Excel
Description
The CLEAN Function is categorized as Text Function in Excel. This function removes all non-printable characters and line breaks from the given text. The CLEAN function was designed to remove the first 32 non-printing characters in the 7-bit ASCII code (values 0 through 31) from text.
The CLEAN function takes one argument which can be a text string or any cell reference or a number. It can also be used as a part of a formula.
Syntax
The syntax of CLEAN function is =CLEAN(text)
Argument or Parameter
text (Required)- can be a text string or cell reference or a number for which we want remove the non-printable charactrer.
Returns
The CLEAN returns text string free from non-printable character and line-breaks..
Example
Example-1
In the example given below, we can see in cell A3 & A4, we have text having line-breaks. In column B you can see the number of total number of character in each cell. In C3 type =CLEAN(A3) and press Enter. Now in cell C3 & C4, function return text without line break . In column D you can see the total number of character after cleaning. See the image below.
Example-2
In this example, we have text in A3 & A4 with hidden character. In Column B we got the total number of character with help of LEN function. Then in column C we used the CLEAN function. In cell C3 type =CLEAN(A3), Press Enter. The text in column C looks same as Column A but in column D we can see the total number of character is different, it is because the text in column A contain hidden characters.
Example-3
In this example, Column A contains text with unwanted character and we need to clean those text. So in cell B3 type =CLEAN(A3) and press Enter. You will get the clean as shown in the image below.
Notes
- The CLEAN function removes the first 32 non-printing characters in the 7-bit ASCII code (values 0 through 31) from text.
- The CLEAN function cannot remove all non printing characters. We can remove them with TRIM, SUBSTITUTE function.
********************************* ~:Support Our Work Financially:~ *********************************
Project File Type: Free
If you think this tutorial helps you to solve your problem and add value to your work, Buy me a Coffee..
************************************************************************************************
|
|
||||
|
CHAR |
CLEAN |
CONCATENATE |
||
|
DOLLAR |
EXACT |
FIND |
FIXED |
LEFT |
|
LEN |
MID |
NUMBERVALUE |
||
|
REPLACE |
REPT |
RIGHT |
SEARCH |
SUBSTITUTE |
|
T |
TEXT |
TRIM |
UNICHAR |
UNICODE |
|
UPPER |
VALUE |
|
|
|
0 Comments