Contents
Yesterday in the marathon 30 Excel functions in 30 days we navigated the entire Excel workbook, opened Excel files and websites, all thanks to the function HYPERLINK (HYPERLINK).
On the 29th day of the marathon, we will study the function CLEAN (CLEAN). Sometimes the data we download from websites or extract from downloaded files contains unwanted characters, and the function CLEAN (CLEAN) allows you to delete them. She won’t do all the hard work for you or help you deal with the mess your kids leave. This feature is perfect for a lazy Sunday!
So, let’s take a closer look at the information on the function CLEAN (PECHSYMV) and study examples of its work. If you have additional information or examples on this feature, please share them in the comments.
Function 29: CLEAN
Function CLEAN (PRINT) removes some non-printing characters from text – characters with codes 0 to 31, 129, 141, 143, 144, and 157.
How can I use the CLEAN function?
Function CLEAN (PRINT) is able to remove some non-printable characters from text, but not all of them. you can use CLEAN (CLEAN) and, if necessary, other functions to:
- Remove some non-printable characters.
- Replace non-printable characters in text.
Syntax CLEAN
Function CLEAN (CLEAN) has the following syntax:
CLEAN(text)
ПЕЧСИМВ(текст)
- text (text) – any information in which you want to remove non-printing characters.
Traps CLEAN
Function CLEAN (PRINT) removes only some non-printable characters from text – characters 0 to 31, 129, 141, 143, 144, and 157. For other non-printable characters, such as non-breaking space character 160, you can use the function SUBSTITUTE (SUBSTITUTE) to replace them with spaces or empty strings.
Example 1: Remove non-printing characters
Function CLEAN (PRINT) removes some non-printable characters, such as characters with ASCII codes 0 to 30. In this example, we will add the characters 9 and 13 to the original text in cell C3.
=CHAR(9)&C3&CHAR(13)
=СИМВОЛ(9)&C3&СИМВОЛ(13)
Function LEN (DLSTR) shows that the number of characters in cell C5 has increased to 15, counting non-printable characters.
Function CLEAN (CLEAN) in cell C7 removes those characters, and the total number of characters decreases by 2, returning to the original value of 13.
=CLEAN(C5)
=ПЕЧСИМВ(C5)
Example 2: Replacing non-printable characters
In a situation where the function CLEAN (CLEAN) cannot delete certain characters, such as characters with codes 127 and 160, you can use the function SUBSTITUTE (SUBSTITUTE) to replace them.
=SUBSTITUTE(E3,CHAR(C3),"")
=ПОДСТАВИТЬ(E3;СИМВОЛ(C3);"")
