CLEAN: Google Sheets Formulae Explained

Google Sheets, a powerful tool in the Google Workspace, offers a variety of formulae to help users manage and manipulate data. One such formula is the CLEAN function, which is designed to remove non-printable characters from text. This function can be particularly useful when dealing with data imported from other sources that may contain hidden characters.

Understanding the CLEAN Function

The CLEAN function in Google Sheets is a text function. This means it is used to manipulate and work with text data. The primary purpose of the CLEAN function is to remove non-printable ASCII characters from a text string. These are characters that cannot be represented visually in a standard text viewer.

Non-printable characters often come from other data sources or systems. For example, when you import data from a database, a CSV file, or copy text from a webpage, you might get non-printable characters. These characters can cause problems when you try to manipulate the text in Google Sheets or when you try to export the data to another system.

How to Use the CLEAN Function

The syntax for the CLEAN function is straightforward. It only requires one argument: the text you want to clean. The formula is written as follows: =CLEAN(text).

The 'text' argument can be a cell reference, or a text string enclosed in quotation marks. If you use a cell reference, the CLEAN function will clean the text in that cell. If you use a text string, the CLEAN function will clean that text string.

Example of the CLEAN Function

Let's say you have a cell (A1) with the text "Hello World" and you want to clean this text. You would use the CLEAN function as follows: =CLEAN(A1). The result would be "Hello World" because there are no non-printable characters in this text.

If the text in A1 contained non-printable characters, the CLEAN function would remove them. For example, if A1 contained "Hello World" with a non-printable character at the end, the CLEAN function would remove this character and return "Hello World".

Limitations of the CLEAN Function

While the CLEAN function is useful, it's important to note that it only removes non-printable ASCII characters. ASCII is a character encoding standard that includes printable characters like letters and numbers, and non-printable characters like control characters.

The CLEAN function does not remove non-ASCII characters, or non-printable characters from other character sets. For example, it won't remove emojis, special symbols, or characters from non-Latin scripts. If you need to remove these types of characters, you'll need to use other methods or functions.

Alternatives to the CLEAN Function

If the CLEAN function doesn't meet your needs, there are other functions and methods you can use to manipulate text in Google Sheets. Here are a few examples:

TRIM Function

The TRIM function removes extra spaces from text. It removes leading and trailing spaces, and reduces multiple spaces between words to a single space. This can be useful if you're dealing with text that has inconsistent spacing.

SUBSTITUTE Function

The SUBSTITUTE function replaces specific text in a text string with other text. You can use this function to replace non-printable characters if you know what they are. However, this requires more work than using the CLEAN function.

REGEXREPLACE Function

The REGEXREPLACE function uses regular expressions to replace text. This is a more advanced function, but it gives you a lot of flexibility. You can use it to replace non-printable characters, as well as perform complex text manipulations.

Conclusion

The CLEAN function in Google Sheets is a simple and effective way to remove non-printable characters from text. It's easy to use, and can save you a lot of trouble when dealing with imported data. However, it's not a catch-all solution, and there may be cases where you need to use other functions or methods. By understanding how the CLEAN function works, and what its limitations are, you can use it more effectively in your own work.

Take Your Data Further with Causal

While the CLEAN function in Google Sheets helps you tidy up your data, Causal takes your data manipulation to the next level. As a dedicated platform for numerical data work, Causal allows you to perform complex calculations, create dynamic visualizations, and share interactive dashboards with ease. If you're ready to enhance your data analysis and presentation, sign up today for free and discover a more streamlined approach to data modelling and forecasting.

Move beyond 

Google Sheets

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.