CLEAN Function in Excel

The CLEAN function is one of the (TEXT) functions of Excel. It removes all nonprintable

characters from text.

We can find this function in TEXT of insert function Tab.

How to use CLEAN function in excel

1. Click on empty cell (like F5 )

2. Click on fx on the below of font word (or press shift+F3)

3. In insert function tab you will see all functions

4. Select TEXT category

5. Select CLEAN function

6. Then select ok

7. In function arguments Tab you will see CLEAN function.

8. Text is any worksheet information from which you want to remove nonprintable characters.

9. You will see the results in formula result section.

Examples of CLEAN function in Excel

Here are 10 examples of how to use the CLEAN function in Excel:

1. Remove non-printable characters from a single cell: =CLEAN(A1) This formula removes all non-printable characters from the text in cell A1.
2. Remove non-printable characters from a range of cells: =CLEAN(A1:A10) This formula removes all non-printable characters from the text in cells A1 through A10.
3. Remove non-printable characters and extract certain characters: =LEFT(CLEAN(A1),3) This formula removes all non-printable characters from the text in cell A1, and then extracts the first three characters using the LEFT function.
4. Remove non-printable characters and extract domain names from email addresses: =RIGHT(CLEAN(A1),LEN(CLEAN(A1))-FIND(“@”,CLEAN(A1))) This formula removes all non-printable characters from the text in cell A1, and then extracts the domain name from an email address.
5. Remove non-breaking spaces from text strings: =SUBSTITUTE(CLEAN(A1),CHAR(160), ” “) This formula removes all non-breaking spaces (ASCII code 160) from the text in cell A1 and replaces them with normal spaces.
6. Remove line breaks from text strings: =SUBSTITUTE(CLEAN(A1),CHAR(10), ” “) This formula removes all line breaks (ASCII code 10) from the text in cell A1 and replaces them with normal spaces.
7. Remove carriage returns from text strings: =SUBSTITUTE(CLEAN(A1),CHAR(13), ” “) This formula removes all carriage returns (ASCII code 13) from the text in cell A1 and replaces them with normal spaces.
8. Remove tabs from text strings: =SUBSTITUTE(CLEAN(A1),CHAR(9), ” “) This formula removes all tabs (ASCII code 9) from the text in cell A1 and replaces them with normal spaces.
9. Remove non-printable characters and concatenate text strings: =CONCATENATE(CLEAN(A1),” “,CLEAN(B1)) This formula removes all non-printable characters from the text in cells A1 and B1, and then concatenates the two cleaned-up strings with a space in-between.
10. Remove non-printable characters and count the number of characters: =LEN(CLEAN(A1)) This formula removes all non-printable characters from the text in cell A1, and then returns the length of the cleaned-up string using the LEN function.

These are just a few examples of how you can use the CLEAN function in Microsoft Excel to manipulate and clean up your data.

With some creativity and experimentation, you can use the CLEAN function in many different ways to suit your specific needs.

How do I use the CLEAN function to remove non-printable characters from my data?

Sure, I’d be happy to explain how to use the CLEAN function in Excel to remove non-printable characters from your data.

The CLEAN function is used to remove any non-printable character from a text string.

Non-printable characters are those characters that cannot be typed on a keyboard or displayed on a screen, such as tab, line break, or carriage return.

Here’s an example of how to use the CLEAN function to remove non-printable characters from a cell containing a text string:

1. Let’s say you have a cell (A1) containing the following text string: “This is a test string with extra spaces and line breaks “.
2. To remove the non-printable characters from this text string, you would enter the following formula in another cell (B1): =CLEAN(A1)
3. Pressing Enter will give you a cleaned version of the text string in cell B1, with all non-printable characters removed: “This is a test string with extra spaces and line breaks”.

As you can see, the CLEAN function has removed the extra spaces and line breaks, as well as any other non-printable characters that may have been present in the original text string.

Another example could be if you have a data set with multiple cells containing various non-printable characters, you can select the range of cells and apply the CLEAN function to all of them at once by following these steps:

1. Select the range of cells where you want to apply the CLEAN function.
2. In the formula bar, type “=CLEAN(” and then select the top-left cell of the range you’ve selected.
3. Type “)” and press Ctrl + Shift + Enter to apply the formula to the entire range of selected cells.

In summary, the CLEAN function is a useful tool for removing non-printable characters from your data in Excel.

By using this function, you can ensure that your data is clean and consistent, making it easier to work with and analyze.

Can the CLEAN function remove all types of non-printable characters from my data?

The CLEAN function in Microsoft Excel is designed to remove all non-printable characters from a text string.

Non-printable characters are those that cannot be displayed on a computer screen or printed, such as tabs, line breaks, and carriage returns.

The CLEAN function removes these characters by converting them to spaces. The syntax for the CLEAN function is:

=CLEAN(text)

Where “text” is the cell reference or text string from which you want to remove non-printable characters.

Here’s an example of how to use the CLEAN function:

Suppose you have a cell with the following text string:

“Hello, world! How are you?”

Note that there is a tab character between “world!” and “How”.

To remove the tab character using the CLEAN function, you would enter the following formula in another cell:

=CLEAN(A1)

Assuming that the original text string is in cell A1, this formula will return “Hello, world! How are you?” with the tab character removed.

However, it’s important to note that the CLEAN function only removes non-printable characters that have ASCII codes less than 32 (including tabs, carriage returns, and line breaks).

If your data contains non-printable characters with ASCII codes greater than or equal to 32, the CLEAN function will not remove them.

For example, if your data includes a bullet point symbol (•) or an em dash (—), the CLEAN function will not remove them because they have ASCII codes of 149 and 151, respectively, which are greater than 32.

In summary, while the CLEAN function is a useful tool for removing many types of non-printable characters from your data, it may not be able to remove all of them.

What is the syntax of the CLEAN function and what arguments does it require?

The syntax of the CLEAN function in Microsoft Excel is:

=CLEAN(text)

The CLEAN function takes only one argument, which is “text.” This argument is the cell reference or text string that you want to clean.

Here’s an example of how to use the CLEAN function:

Let’s say you have a column of data that contains text strings with non-printable characters such as line breaks, tabs, and carriage returns.

You want to remove these characters from the text strings to make the data easier to read.

To do this, you can use the CLEAN function. Assume that the data is in column A, starting from row 1.

In cell B1, you can enter the following formula:

=CLEAN(A1)

This will remove all non-printable characters from the text string in cell A1 and return the cleaned text in cell B1.

You can then copy this formula down to the remaining cells in column B to clean the entire column of data.

It’s important to note that the CLEAN function only removes non-printable characters with ASCII codes less than 32, including tabs, carriage returns, and line breaks.

It will not remove printable characters such as spaces, letters, or numbers.

In summary, the CLEAN function is a simple yet powerful tool for removing non-printable characters from text strings in Excel.

Its syntax is straightforward, taking only one argument – the text string or cell reference you wish to clean.

Is it possible to use the CLEAN function to remove specific non-printable characters only?

No, the CLEAN function in Microsoft Excel cannot be used to remove specific non-printable characters only.

The purpose of the CLEAN function is to remove all non-printable characters from text strings, not just specific ones.

If you need to remove specific non-printable characters from your data, you may need to use a different function or a combination of functions. One option is to use the SUBSTITUTE function, which can replace specific characters within a text string.

For example, if you want to remove all instances of the tab character (ASCII code 9) from a text string, you can use the SUBSTITUTE function with a blank space as the replacement value.

The formula would look like this:

=SUBSTITUTE(text, CHAR(9), ” “)

In this formula, “text” is the cell reference or text string that you want to clean. CHAR(9) represents the tab character, and ” ” is the replacement value. This will replace all tabs with spaces in the text string.

You can also use the REPLACE function to remove specific characters from a text string.

The syntax for the REPLACE function is:

=REPLACE(old_text, start_num, num_chars, new_text)

In this formula, “old_text” is the cell reference or text string that you want to modify.

“Start_num” is the position within the text string where you want to start replacing characters.

“Num_chars” is the number of characters you want to replace, and “new_text” is the new text you want to insert in place of the old text.

For example, to remove the first three characters from a text string, you can use the following formula:

=REPLACE(text, 1, 3, “”)

This will replace the first three characters in the text string with an empty string, effectively removing them.

In summary, while the CLEAN function is useful for removing all non-printable characters from text strings, it cannot be used to remove specific non-printable characters only.

To remove specific characters, you may need to use the SUBSTITUTE or REPLACE function, or a combination of both.

Can the CLEAN function remove leading or trailing spaces as well?

No, the CLEAN function in Microsoft Excel does not remove leading or trailing spaces.

The purpose of the CLEAN function is to remove all non-printable characters from text strings, but it does not affect printable characters such as spaces.

However, if you want to remove leading or trailing spaces along with non-printable characters, you can use the TRIM function in combination with the CLEAN function.

The TRIM function removes any leading or trailing spaces from a text string.

The syntax for the TRIM function is:

=TRIM(text)

In this formula, “text” is the cell reference or text string that you want to trim.

To remove both leading/trailing spaces and non-printable characters using the CLEAN and TRIM functions, you can nest them together in a formula like this:

=TRIM(CLEAN(text))

This formula first cleans up the text by removing all non-printable characters using the CLEAN function, and then trims any leading or trailing spaces using the TRIM function.

Here’s an example of how to use the CLEAN and TRIM functions together:

Assume that you have a cell with the following text string:

” Hello, world! “

Note the leading and trailing spaces in the text string.

To remove both the spaces and any non-printable characters from the text string, you can use the following formula:

=TRIM(CLEAN(A1))

Assuming that the original text string is in cell A1, this formula will return “Hello, world!” with all non-printable characters, leading and trailing spaces removed.

In summary, while the CLEAN function alone cannot remove leading or trailing spaces, you can use it in combination with the TRIM function to remove both non-printable characters and leading/trailing spaces from a text string.

Does the CLEAN function modify the original data or create a new copy of it?

The CLEAN function in Microsoft Excel does not modify the original data. It creates a new copy of the text string with all non-printable characters removed.

When you use the CLEAN function, it returns a cleaned-up version of the text string without affecting the original data.

This means that if you want to keep the original data and work with the cleaned-up version separately, you need to copy or move the cleaned-up version to a new cell or worksheet.

For example, suppose you have a cell with the following text string:

“Hello, world! “

Note the trailing spaces at the end of the text string.

To remove the trailing spaces using the CLEAN function, you can enter the following formula in another cell:

=CLEAN(A1)

Assuming that the original text string is in cell A1, this formula will return “Hello, world!” with the trailing spaces removed. However, the original text string in cell A1 remains unchanged.

If you want to keep both the original text string and the cleaned-up version, you can copy the cleaned-up version to a new cell.

To do this, simply select the cell containing the cleaned-up version, press Ctrl+C to copy it, then select the destination cell and press Ctrl+V to paste it.

In summary, the CLEAN function in Microsoft Excel does not modify the original data.

It creates a new copy of the text string with all non-printable characters removed.

If you want to keep both the original data and the cleaned-up version, you need to copy or move the cleaned-up version to a new cell or worksheet.

Can I use the CLEAN function with other functions in Excel to manipulate my data further?

Yes, you can use the CLEAN function in combination with other functions in Microsoft Excel to manipulate your data further.

For example, you can use the CLEAN function to remove all non-printable characters from a text string and then use the LEFT or RIGHT function to extract a specific number of characters from the cleaned-up string.

Here’s an example:

Assume that you have a column of data containing product codes in the format “ABC-123-XYZ”.

You want to extract only the first three characters from each code (i.e., “ABC”).

To do this, you can use the LEFT function along with the CLEAN function. Assuming that the data is in column A, starting from row 1, you can enter the following formula in cell B1:

=LEFT(CLEAN(A1), 3)

This formula cleans up the text string in cell A1 by removing all non-printable characters using the CLEAN function, and then extracts the first three characters using the LEFT function.

You can copy this formula down to the remaining cells in column B to extract the first three characters from all the product codes.

Similarly, you can use the RIGHT function in combination with the CLEAN function to extract the last few characters from a text string.

Here’s an example:

Assume that you have a column of data containing email addresses in the format “firstname.lastname@email.com“. You want to extract only the domain name (i.e., “email.com“) from each address.

To do this, you can use the RIGHT function along with the CLEAN function. Assuming that the data is in column A, starting from row 1, you can enter the following formula in cell B1:

=RIGHT(CLEAN(A1), LEN(CLEAN(A1))-FIND(“@”,CLEAN(A1)))

This formula cleans up the text string in cell A1 by removing all non-printable characters using the CLEAN function, and then extracts the domain name using the RIGHT function.

The LEN and FIND functions are used to determine the length of the domain name based on the position of the “@” symbol in the email address.

You can copy this formula down to the remaining cells in column B to extract the domain names from all the email addresses.

In summary, you can use the CLEAN function in combination with other functions in Microsoft Excel to manipulate your data further.

By cleaning up text strings first and then extracting specific characters or substrings using other functions, you can gain more insights and perform more advanced analysis on your data.

Are there any limitations or potential issues when using the CLEAN function in Excel?

Yes, there are a few limitations and potential issues to consider when using the CLEAN function in Microsoft Excel.

1. Limitations on character types: The CLEAN function is designed to remove non-printable characters from text strings, but it cannot remove certain printable characters such as spaces, letters, or numbers.
   • It can only remove non-printable characters with ASCII codes less than 32, including tabs, carriage returns, and line breaks.
2. Truncation of long text strings: If you use the CLEAN function on a very long text string, it may be truncated to fit within the maximum allowable length of a cell in Excel (32,767 characters).
   • This can result in loss of data or errors in your analysis.
3. Loss of original formatting: The CLEAN function removes all non-printable characters from a text string, which may include formatting elements such as font style, color, or size.
   • This can result in loss of original formatting in your data.
4. Impact on performance: If you apply the CLEAN function to a large number of cells or a large amount of data, it can slow down your workbook’s performance, especially if you combine it with other complex formulas or functions.
5. Potential for errors: Like any formula or function in Excel, the CLEAN function can produce errors if the input data is not properly formatted or structured.
   • For example, if you apply the CLEAN function to a cell that contains a number or date, you may get unexpected results.

To avoid these limitations and potential issues, it’s always a good practice to test your formulas and functions thoroughly before applying them to your entire dataset.

You may also want to consider breaking up your data into smaller chunks or using more powerful tools like VBA macros or Power Query to clean and manipulate your data.

In summary, while the CLEAN function is a useful tool for removing non-printable characters from text strings in Excel, it has some limitations and potential issues that you need to be aware of.

By understanding these limitations and taking appropriate measures to address them, you can ensure the accuracy and reliability of your data analysis.

How can I troubleshoot issues when using the CLEAN function in Excel?

When using the CLEAN function in Microsoft Excel, you may encounter issues or errors that need to be troubleshooted.

Here are some common issues and how to troubleshoot them:

1. Non-printable characters not removed: If the CLEAN function does not remove all non-printable characters from a text string, check the ASCII codes of the characters to see if they are greater than 32.
   • Remember, the CLEAN function only removes characters with ASCII codes less than 32.
2. Unexpected results: If you get unexpected results when applying the CLEAN function, double-check the input data to make sure it’s formatted correctly.
   • The CLEAN function expects a text string as input, so if you apply it to a number or date, for example, you may get unexpected results.
3. Truncated text strings: If the CLEAN function truncates long text strings, you can try splitting the original text string into smaller parts and applying the CLEAN function to each part separately.
   • You may also want to consider using a more powerful tool like VBA macros or Power Query to handle large datasets.
4. Loss of formatting: If the CLEAN function removes formatting elements such as font style, color, or size, you can try using other functions or tools to preserve the formatting.
   • For example, you can use the SUBSTITUTE function to replace specific characters without affecting formatting.
5. Performance issues: If using the CLEAN function slows down your workbook’s performance, you can try optimizing your formulas by minimizing unnecessary calculations, reducing the number of cells referenced, or using array formulas or pivot tables to summarize data.

In addition to these troubleshooting tips, you can also use Excel’s built-in error messages and debugging tools to identify and fix issues with your formulas.

For example, you can use the Evaluate Formula feature to step through your formula and see how Excel is evaluating each part of it.

In summary, when troubleshooting issues with the CLEAN function in Excel, it’s important to understand the limitations and potential issues of the function, double-check your input data, and use other functions or tools to handle specific situations.

By taking a systematic approach and using Excel’s built-in features and debugging tools, you can identify and fix issues with your formulas and ensure the accuracy and reliability of your data analysis.

Related posts:

Excel VALUE Function Keyboard Shortcuts for Applying Borders in Excel UNIQUE Function in Excel Excel LEFT Function