CLEAN Function in Excel (Table of Contents)
CLEAN in Excel
As the name itself suggests CLEAN means, you need to clean something. It could text, number or something else. The more messier the data the more time you take to alter your data. Usually, we copy and paste the data from web browsers. While copying the data from web browsers we copy unwanted characters like non-printable characters. Some of the non-printable characters are as shown in the below image.
In order to remove all non-printable characters, we can use a CLEAN function in excel. Excel CLEAN function can remove the first 32 7-bit ASCII code characters from the text. First 32 characters.
These kinds of characters are found at the start of the data imported into excel from other web applications. There are totally 37 7-bit ASCll code characters out of which CLEAN function can clean the first 32 characters.
The remaining character values are numbers 127, 129, 141, 143, 144 and 157. The characters generated by these codes cannot be removed by CLEAN function.
A CLEAN function can be applied to text, strings, numbers etc…. One important thing we need to notice here is when a CLEAN function is applied to numbers it automatically converts the numbers to text format.
CLEAN is vastly used in data-driven jobs where people more often deal with research and development data. Marketing is the team, which uses the data to send campaigns and writer contents to their campaign. While writing the content to the campaign they research more on the internet and copy directly from the web browsers and they copy special characters along with the data. In the below examples we will see the practical usage of the CLEAN function in the practical world.
CLEAN Formula in Excel
Below is the CLEAN Formula in Excel :
CLEAN Formula in Excel includes only one parameter i.e. TEXT.
- TEXT: The desired text you wish to remove the non-printable characters. This parameter or argument can be applied by giving reference to a cell or entering the data directly into the argument by using double quotes.
How to Use the CLEAN Function in Excel?
CLEAN Function in Excel is very simple and easy to use. Let understand the working of CLEAN Function in Excel by some example.
4.8 (2,613 ratings)
Suppose you are working in a marketing department as Marketing Executive and you, the manager asked you to collect some of the important growth factors in the real estate sector in India.
You have researched enough and collected below data after surfing on the internet for 2 days and the data looks messier than the usual.
Since you have directly copied from the browser you found these non-printable characters, which are marked in the red boxes in the above image.
It is no brainer that you need to remove those extra special characters before submitting the data to your manager. You cannot do this manually. Copy the data to excel file and apply a CLEAN function to it.
Step 1: Copy the data to excel sheet.
Step 2: Apply the CLEAN function in cell B2 and the formula should like the below one.
So the output will be :
We have applied the CLEAN Function in column B by dragging it. All the special characters are of 7-bit ASCll code characters.
A CLEAN function can clean the first 32 7-bit ASCll code characters from the text.
Below is the list of ASCll characters.
As a marketing executive, you work on research on so many things. While researching you found below important contact numbers of C level executives. C level means CEO, CFO, COO etc… Along with the contact numbers, you copied non-printable characters also.
If you look at these telephone numbers, you cannot say the full numbers here. These are not special characters rather a special font name. All the special characters are given a font name called Wingdings.
For these kinds of cases, also we can apply a CLEAN function to clean our data. So now, let us go ahead and apply a CLEAN function to our telephone numbers.
So the output will be :
After applied CLEAN function to our data, it converted to numbers i.e. converted from special font character to normal character. So the final output will occur by dragging cell B2 :
Since we applied for the CLEAN function number, a format is removed and text format is inserted. In order to retain number format, we need to use the VALUE formula before CLEAN function.
So the output will be :
So the final output will occur by dragging cell B2 :
So, VALUE function helps in converting our supplied value to number format.
This is how CLEAN can be vital in cleaning our messy data into a beautiful presentable data.
Things to Remember About the CLEAN Function in Excel
- CLEAN Function in Excel can remove 0 to 32 7-bit ASCll code characters from the list.
- CLEAN Function available in Excel 2003 and later versions.
- The addition non-printable characters generated by the codes 127, 129, 141, 143, 144 and 157.
- Some non-printable characters are not visible at all but a CLEAN function can find that non-visible character and helps to remove the same.
This has been a guide to CLEAN in Excel. Here we discuss How to use CLEAN Function in Excel along with examples and downloadable excel template. You may also look at these useful functions in excel –