The CLEAN function is a powerful tool in Google Sheets that allows you to remove non-printable characters from cells or arrays. In this article, we will explore the use of this function and how it can improve your spreadsheet experience.
Non-Printable Characters in Google Sheets
Non-printable characters, such as Backspace, Line Feed, Carriage Return, Horizontal Tab, and Vertical Tab, are ASC-II characters that are not visible when printed. To see these characters in action, you can refer to the provided table in this Wiki containing the current Unicode characters used in Google Sheets. By applying the following formula to cell A1: =ArrayFormula(char(row(A1:A50))), you can observe the characters from rows 1 to 31, which represent the non-printable characters. The remaining rows, from A32 to A50, will display the printable characters.
Example to Non-Printable Characters in a Text in Sheets
Non-printable characters may be present in your sheet if you import data from external sources or manually add them using the CHAR function. For instance, suppose you have the string “Info Inspired” in cell A1. By using the CHAR(10) function, you can move the word “Inspired” to a new line within cell A1: ="INFO"&CHAR(10)&"INSPIRED". The CHAR function converts the number 10 to the character ‘Line Feed’ according to the Unicode table.
Syntax and Examples of the CLEAN Function in Google Sheets
The syntax of the CLEAN function is as follows: CLEAN(text). The text argument represents the text string in a cell or array from which you want to remove the non-printable characters.
To illustrate, if you want to remove non-printable characters from cell A1, you can use the formula: =CLEAN(A1). Similarly, if you want to apply the CLEAN function to an array, you can use the following formula: =ArrayFormula(CLEAN(A1:A10)).
Just like the TO_TEXT function, the CLEAN function can also convert numbers to text. For example, assuming cells A1:A10 contain numbers from 1 to 10, you can use the CLEAN formula to convert these numbers to text: =ArrayFormula(CLEAN(A1:A10)).
Please note that the CLEAN function cannot remove whitespaces from a cell. To accomplish this, you can use either the TRIM function or the “Data > Trim Whitespace” command from the menu.
That’s all you need to know about the CLEAN function in Google Sheets. Enjoy using this powerful tool to enhance your spreadsheet experience!
For more information, visit Crawlan.com.
