Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
Among Excel’s useful functions for data analysis, CLEAN is an interesting one. The CLEAN function removes non-printable ASCII characters from a text string.
The purpose of the CLEAN function is to sanitize data by getting rid of non-printable characters that can cause issues in data processing or display. It’s a great tool when working with imported or copied data, which can often contain these unwanted characters.
Creating formulas in Excel, such as the CLEAN function, is a straightforward task. The syntax is:
=CLEAN(text)
The CLEAN function requires only one parameter:
text: This is the text string from which you want to remove non-printable characters.
The CLEAN function returns the text string without any non-printable ASCII characters.
This function is helpful when dealing with data imported from other applications that may include non-printable characters.
As a standard Excel function, CLEAN is available across all versions of Excel.
Let’s say you have a text string in cell A1 that contains non-printable characters. To remove them, use the formula =CLEAN(A1).
Consider a scenario where you’ve copied text from a webpage into cell A1, and it includes non-printable characters. To clean the text, use =CLEAN(A1).
Suppose you’re working with a dataset imported from another application that includes non-printable ASCII characters in cell A1. To remove these, use =CLEAN(A1).
Perhaps you have text combined with non-printable characters in cell A1 and want to use this cleaned text in a formula. You can use the CLEAN function within another function, such as =CONCATENATE(“Cleaned Text: “, CLEAN(A1)).
Lastly, consider a situation where you have a list of text strings in column A with non-printable characters. You could use =CLEAN(A1) in B1, and then copy this formula down to clean the entire list.
Remember, CLEAN only removes non-printable ASCII characters (0-31). To remove other unwanted characters, you may need to use additional functions, such as SUBSTITUTE or TRIM.
The main limitation of the CLEAN function is that it only removes ASCII characters from 0 to 31. Any other non-printable characters won’t be removed.
The CLEAN function doesn’t typically produce errors unless it’s used incorrectly within another formula.
When using CLEAN, it’s a good idea to always check your data after applying it. Not all non-printable characters are removed, so additional cleaning steps might be required.
Other related Excel functions include:
TRIM: Removes extra spaces from text.
SUBSTITUTE: Replaces specific characters or strings within text.
The CLEAN function is commonly used with:
TRIM: After using CLEAN, you can use TRIM to remove any extra spaces left in the text.
SUBSTITUTE: If there are specific non-printable characters that CLEAN doesn’t remove, SUBSTITUTE can help.
Yes, the CLEAN function is available in all versions of Excel.
No, CLEAN only removes non-printable ASCII characters from 0 to 31.
The CLEAN function returns the text string without any non-printable ASCII characters.
To sum up, the CLEAN function is a handy tool for data cleaning, making it a part of the best Excel functions. Use it to remove non-printable characters and keep your data looking clean and professional.