How to Use Excel's Clean Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's Clean function removes all of the non-printable characters from a string. It is often used when importing text from other applications that may not be compatible with your operating system. The following steps will show how to use Excel's Clean function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of Clean. It is Clean(raw_text) where raw_text is the string from which the non-printable characters are to be removed.

Step2

Understand the purpose of the Clean function. It was originally designed to remove the first 32 characters in the ASCII code from text because these values cannot be printed. However, values 127, 129, 141, 143, 144 and 157 in the Unicode character set also are non-printing but are not removed by Clean.

Step3

Look at a simple example of the Clean function. Enter =CHAR(6)&"test text"&CHAR(6) in cell A1 and enter =Clean (A1) in cell A2. The result in cell A2 will be "test text." Note the use of CHAR to insert non-printable characters in the sample text and their subsequent removal by Clean.

Step4

Study a more complex use of the Clean function. Combine Substitute with Clean to remove all non-printing characters from text. This will include the Unicode characters listed in Step 2 that may cause problems when filtering, searching and sorting.

Step5

Use Substitute six times first to replace each of the six high value non-printing Unicode characters with one of the first 32 ASCII characters. For example, the first use would look like this: Substitute (raw_text,CHAR(127),CHAR(7)). Then run Clean once to remove all of the non-printing characters.