CLEAN Excel Function

The CLEAN function in Excel is available in the Formulas tab. We can click Formulas 🡪 Text 🡪 CLEAN to use it.

The CLEAN function is not working in Excel, perhaps because the nonprinting characters we require to eliminate in the given text are not present in the first 32 7-bit ASCII code.

We can use the CLEAN() in Excel VBA in the following way.

Let us see the steps with an example.

Consider the below table. Assume we have a source data set in column A, imported from an external application.



And column B shows whether the individual cells in column A contain nonprintable characters, additional spaces, and line breaks.

Suppose we must remove such unwanted characters from the source data and display the required results in the target cell range C2:C5. Then here is how we can use the CLEAN function in Excel VBA to achieve the desired outcome in range C2:C5.

Step 1: With the required active worksheet opens, press the keyboard shortcut Alt + F11 to open the VBA Editor.



Step 2: Next, choose the specific VBAProject in the left menu and click Insert –> Module to open the Module1 window.



Step 3: Enter the VBA code to remove nonprintable characters, additional spaces, and line breaks from the given text in the Module1 window.



Step 4: Next, click the Run Sub/UserForm button to run the code commands.



Step 5: Finally, open the active worksheet to view the output in the target cell range C2:C5.