What Is CLEAN Function In Excel?
The CLEAN function in Excel is an inbuilt Text function that eliminates all the nonprintable characters from the given text. Users can apply the CLEAN Excel function on texts imported from other sources that contain characters our OS might not be able to print. While the CLEAN() clears away such characters, we can also use it to remove line breaks in a given text string.
For example, consider the below table. It shows a list of text values containing nonprintable characters and line breaks in column A and the formulas to derive them in column B.
And suppose we require to display the column A texts in the target cell range C2:C5 without the nonprintable characters. Then, considering the CLEAN Excel function definition, we can apply the CLEAN() to the specific target cells.
The column C target cell formulas containing the CLEAN Excel function remove characters that our OS can not print, such as CHAR(15), CHAR(12), and CHAR(21). And the CLEAN() output in each target cell shows those characters, we can print using the Print command that the application offers.
Also, the CLEAN Excel function removes excel line breaks [Refer to row 5 in the table depicted in the above image] and returns a text with regular spaces.
Table of contents
Key Takeaways
- The CLEAN Excel function eliminates all the nonprintable characters.
- CLEAN excel formula is =CLEAN(text) where, ‘text’ indicates the text string that has to be removed.
- Users can use the CLEAN() when working with data imported from other applications containing characters that our OS cannot print. We can also use the function to eliminate line breaks in string values.
- The CLEAN() takes one mandatory argument, text, as input.
- Using the CLEAN() with TRIM and SUBSTITUTE functions makes eliminating nonprintable characters, non-breaking spaces, and additional spaces in a text more straightforward. Also, when used with other Excel functions, such as IF, the CLEAN() gives excellent results.
CLEAN() Excel Formula
The CLEAN Excel formula is:
where,
- text: The text from which we require to remove the nonprintable characters.
The text argument in the CLEAN Excel formula is mandatory. It can be the text value in double quotations or an excel cell reference to the specific text.
Below are a few critical facts of the CLEAN Excel function definition we should know before using the CLEAN() in our tasks.
- The CLEAN() removes 0 to 31 nonprinting characters in the 7-bit ASCII code from a text.
- Sometimes the text values can include invisible nonprinting characters. We can confirm if the given text contains such characters by comparing the source text with the text value the CLEAN Excel formula returns.
- The Unicode character set contains additional nonprintable characters, which are 127, 129, 141, 143, 144, and 157. And when we try removing these specific nonprinting characters using the CLEAN(), the function can not eliminate them on its own.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use CLEAN Excel Function?
We can adhere to the following steps to use the CLEAN Excel Function.
- Firstly, ensure the nonprinting characters we require to remove from the source text data are among the first 32 nonprintable characters in the 7-bit ASCII code.
- Then, select the target cell and enter the CLEAN().
- Finally, press Enter to view the printable text characters that the CLEAN function returns as the output in the target cell.
The following illustration explains the steps to use CLEAN().
The below table contains an employee list in column A, with their employee ID details imported from another source in column B. And the imported data contains nonprintable and unwanted characters, with some being invisible, such as in cells B4 and B7.
Suppose we have to display the valid employee IDs in cell range C2:C7. Then we can use the CLEAN() in the target cells to eliminate the nonprinting characters from the imported data, even if they are invisible.
Step 1: Select the target cell C2, enter the CLEAN(), and press Enter.
=CLEAN(B2)
The other way to execute the CLEAN() is to select the target cell C2 and click Formulas 🡪 Text 🡪 CLEAN to open the Function Arguments window.
Enter the text argument value, B2, in the Function Arguments window.
Finally, click OK in the Function Arguments window to execute the function.
Step 2: Drag the fill handle downwards to copy the formula in the cell range C3:C7.
In the above CLEAN Excel function example, cell range B2:B3 and B5:B6 contain visible nonprintable characters. And so, we can see them removed from the employee IDs in the respective target cells.
However, cells B4 and B7 include invisible nonprintable characters, CHAR(2) and CHAR(9), respectively.
The CLEAN() removes the above-mentioned invisible characters.
For example, if we paste special the value in cell B7 to remove the formula, we will see a tab space between the terms GTPL and 5264.
And for that, we can select cell B7 and press Ctrl + C.
Then press the keys Alt + E + S + V one after the other to open the Paste Special window and paste the cell B7 content as a value.
And once we click OK in the Paste Special window, the output in cell B7 will be:
We can see a tab space between the two terms in the Formula Bar.
However, when we paste special the value in cell C7, using the above method, the Formula Bar shows the valid employee ID without the unwanted space.
Thus, the corresponding target cells’ formulas containing the CLEAN function remove characters nonprintable and invisible.
Examples
The following examples show the different scenarios where we can apply the CLEAN Excel function.
Example #1
The below table contains inventory details for different warehouses.
Suppose we imported the above data from another source, leading to column B having nonprintable characters, CHAR(12), CHAR(15), and CHAR(22). Also, assume some cells in the range B2:B7 contain additional spaces, CHAR(32).
Then we can remove these unwanted characters using the CLEAN Excel function in TRIM() in the specific target cells, in this case, cell range C2:C7. While the CLEAN() will eliminate the nonprintable characters, the TRIM() will remove the extra spaces in the text values.
Step 1: Select the target cell C2, enter the formula provided in the Formula Bar in the below image, and press Enter.
Step 2: Drag the fill handle downwards to copy the formula in cell range C3:C7.
Step 3: Select cell range C2:C7 and paste special the values to remove the formulas containing the CLEAN Excel function and TRIM() [Refer to the previous section to know the steps to paste special values].
We will see the error warning, as depicted in the image below.
The reason is that the numbers in the target cells are in text format. Therefore, we can click on the error warning symbol and pick the second option to convert them to numbers.
And the final output in column C will have the inventory data in numbers, without the characters.
Example #2
Consider the below table containing a list of US states and capitals in column A. But the data in each cell also includes nonprintable characters and non-breaking spaces.
Suppose we have to remove the nonprintable characters and non-breaking spaces from the data in column A and display the corrected data in the target cell range B2:B6. Then, we can apply the CLEAN() with TRIM and SUBSTITUTE Excel functions in the target cells to achieve the desired output.
Step 1: Select the target cell B2, enter the below formula containing the CLEAN, SUBSTITUTE, and TRIM excel functions, and press Enter.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),””)))
First, the SUBSTITUTE() replaces each non-breaking space, CHAR(160), with a blank. Then the CLEAN Excel function eliminates the nonprinting character, in this case, CHAR(12). And finally, the TRIM() removes the unwanted spaces from the string value, leaving single spaces between the terms in the string to return the text value as Austin- Texas.
Step 2: Drag the fill handle downwards to copy the formula in cell range B2:B6.
Thus, the CLEAN() frees the source data from the nonprinting characters in column B.
Example #3
This CLEAN Excel function example shows how the CLEAN() works with the IF().
Assume we imported the list of most-valuable tech companies for 2022 from another application.
And we require to check if the texts in the list contain visible and invisible characters that might not get printed with our OS and display the check results in cell range B2:B11. In other words, we need to verify if the source text values in cell range A2:A11 are clean or not clean.
In such a case, we can apply the CLEAN Excel function in the IF() to perform the required check.
Step 1: Select the target cell B2, enter the below formula, and press Enter.
=IF(CLEAN(A2)=A2,”Clean”,”Not Clean”)
In the above IF formula, the CLEAN() returns the cell A2 text without the nonprintable characters, Apple. Then the IF() checks if the CLEAN() output equals the cell A2 text value. As the condition does not hold, the IF() returns Not Clean as the output in the target cell B2.
Step 2: Drag the fill handle downwards to copy the formula in range B3:B11.
In the case of rows 7 and 8, the CLEAN() outputs are equal to the cells A7 and A8 text values. Hence, as the IF conditions in the respective target cells B7 and B8 hold, the formulas in them return the value Clean.
Important Things To Note
- The CLEAN Excel function clears the nonprinting characters from the given string or text value.
- The CLEAN function removes the first 32 (0 to 31 values) 7-bit ASCII code.
- The CLEAN() can also remove invisible nonprintable characters from the source data. Thus, we can use the CLEAN function to check if a given string or text value contains invisible nonprintable characters.
- The Unicode character set contains six additional nonprinting characters. But the CLEAN() cannot remove them by itself.
Frequently Asked Questions
The CLEAN function in Excel is available in the Formulas tab. We can click Formulas 🡪 Text 🡪 CLEAN to use it.
The CLEAN function is not working in Excel, perhaps because the nonprinting characters we require to eliminate in the given text are not present in the first 32 7-bit ASCII code.
We can use the CLEAN() in Excel VBA in the following way.
Let us see the steps with an example.
Consider the below table. Assume we have a source data set in column A, imported from an external application.
And column B shows whether the individual cells in column A contain nonprintable characters, additional spaces, and line breaks.
Suppose we must remove such unwanted characters from the source data and display the required results in the target cell range C2:C5. Then here is how we can use the CLEAN function in Excel VBA to achieve the desired outcome in range C2:C5.
Step 1: With the required active worksheet opens, press the keyboard shortcut Alt + F11 to open the VBA Editor.
Step 2: Next, choose the specific VBAProject in the left menu and click Insert –> Module to open the Module1 window.
Step 3: Enter the VBA code to remove nonprintable characters, additional spaces, and line breaks from the given text in the Module1 window.
Step 4: Next, click the Run Sub/UserForm button to run the code commands.
Step 5: Finally, open the active worksheet to view the output in the target cell range C2:C5.
Download Template
This article must be helpful to understand the CLEAN Excel Function, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Clean Excel Function. Here we learn to remove non-printable characters using CLEAN Formula, with examples & a downloadable excel template. You can learn more from the following articles –
Leave a Reply