How to Use the CLEAN Function in Microsoft Excel to Remove Non-Printable Characters

When importing data into Microsoft Excel from external sources—such as legacy ERP databases, mainframe feeds, web scraping tools, or raw CSV files—you often encounter phantom formatting errors. Text may refuse to match in XLOOKUP or VLOOKUP formulas, line heights may expand unexpectedly, or formulas like LEN may report mysterious extra characters that you cannot see on screen.

These issues are typically caused by invisible, non-printable control characters. Rather than manually editing hundreds of rows or writing complicated VBA scripts, you can use Excel’s built-in CLEAN function. Here is how to use the CLEAN function in Microsoft Excel to purge unprintable characters and sanitise your datasets.

What Is the CLEAN Function and How Does It Work?

The CLEAN function is specifically engineered to remove the first 32 non-printable characters in the 7-bit ASCII character set (ASCII decimal values 0 through 31). In computing history, these codes represent transmission and device controls, such as:

• CHAR(0): Null character
• CHAR(7): Bell / alert signal
• CHAR(9): Horizontal tab
• CHAR(10): Line feed / newline
• CHAR(11): Vertical tab
• CHAR(12): Form feed / page break
• CHAR(13): Carriage return
• CHAR(27): Escape character

The syntax for the CLEAN function is simple and accepts a single argument:

=CLEAN(text)

Where text is the target string, cell reference, or array containing the unprintable characters you want to strip.

Basic Example: Removing Stray Line Breaks and Carriage Returns

Suppose cell A2 contains customer contact data exported from an older database where a rogue carriage return and line feed were appended to an address string:

"45 Park Lane [CR][LF]"

To strip the unprintable carriage return and line feed, enter this formula in cell B2:

=CLEAN(A2)

Excel returns "45 Park Lane" without the trailing newline codes, restoring regular single-line cell height.

The Power Combination: TRIM and CLEAN

In real-world data cleansing, unprintable characters rarely appear in isolation; they are almost always accompanied by irregular whitespace, leading spaces, and trailing padding. Because CLEAN only removes control codes (ASCII 0–31) and leaves standard space characters intact, combining it with TRIM is standard best practice:

=TRIM(CLEAN(A2))

In this nested formula:

1. CLEAN(A2) first removes all unprintable control codes (such as tabs, line breaks, and nulls).
2. TRIM(...) then inspects the resulting text, stripping all leading and trailing spaces while collapsing multiple consecutive spaces between words into a single standard space.

Handling Non-Breaking Spaces (ASCII 160)

A frequent stumbling block for Excel users occurs when =TRIM(CLEAN(A2)) appears to leave stubborn spaces behind. This happens because web pages and HTML reports frequently use non-breaking spaces ( ), which map to ASCII value 160.

Because the 7-bit ASCII table only extends to 127, CLEAN ignores code 160. To eradicate non-breaking spaces alongside control characters, nest the SUBSTITUTE function to convert code 160 into a standard space (code 32) before trimming:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

This triple-nested formula represents the industry-standard “universal sanitiser” for messy corporate web and database exports.

Using CLEAN with Dynamic Arrays in Modern Microsoft 365

If you are using Microsoft 365, Excel 2021, or Excel for the Web, you do not need to drag formulas down manually across thousands of rows. The CLEAN function natively supports dynamic array spilling:

=TRIM(CLEAN(SUBSTITUTE(A2:A500, CHAR(160), " ")))

Entering this single formula into cell B2 automatically populates the entire range down to row 500, recalculating instantly whenever source data updates.

How to Convert Cleaned Formulas into Permanent Static Values

Once your dataset has been cleansed, you will likely want to replace the original messy source data and remove the formula dependency:

1. Select the column or range containing your =CLEAN(...) formulas.
2. Press Ctrl + C (or right-click and select Copy).
3. Right-click the target cell where you want the permanent clean data to live (either over the original raw column or a designated clean column).
4. Under the Paste Options section, click the Values icon (the clipboard displaying 123) or press Ctrl + Shift + V.
5. You can now safely delete the helper formula column. Your text is now completely sanitised and ready for database lookups, pivot tables, and sorting.