Abdul Alim: a little bit learning, let's go

Excel, VBA and Power BI tutorials 

CLEAN Formula

Definition

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.

Purpose

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.

Syntax

Creating formulas in Excel, such as the CLEAN function, is a straightforward task. The syntax is:

=CLEAN(text)

Parameters

The CLEAN function requires only one parameter:

• text: This is the text string from which you want to remove non-printable characters.

Returns

The CLEAN function returns the text string without any non-printable ASCII characters.

Usage Notes

This function is helpful when dealing with data imported from other applications that may include non-printable characters.

Availability

As a standard Excel function, CLEAN is available across all versions of Excel.

Example #1

Let’s say you have a text string in cell A1 that contains non-printable characters. To remove them, use the formula =CLEAN(A1).

Example #2

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).

Example #3

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).

Example #4

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)).

Example #5

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.

Tips and Tricks

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.

Limitations

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.

Common Errors and Solutions

The CLEAN function doesn’t typically produce errors unless it’s used incorrectly within another formula.

Best Practices

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.

List of Related Functions

Other related Excel functions include:

• TRIM: Removes extra spaces from text.

• SUBSTITUTE: Replaces specific characters or strings within text.

Frequently Used with Formulas

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.

Frequently Asked Questions

Q. Is the CLEAN function available in all versions of Excel?

Yes, the CLEAN function is available in all versions of Excel.

Q. Does the CLEAN function remove all non-printable characters?

No, CLEAN only removes non-printable ASCII characters from 0 to 31.

Q. What does the CLEAN function return?

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.