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

Importing external data often introduces frustrating, invisible non-printable characters that disrupt your Excel formulas. While standard database exports are essential for gathering raw information, they frequently carry hidden formatting baggage. Fortunately, leveraging the CLEAN function grants immediate data uniformity, restoring formula integrity and lookup accuracy instantly.

As a key stipulation, note that CLEAN is designed to remove only the first 32 non-printable ASCII characters (values 0 through 31). Web-based data often contains non-breaking spaces (ASCII 160), which require pairing CLEAN with SUBSTITUTE. For example, nested formulas like =TRIM(CLEAN(A2)) solve most common import errors.

Below, we outline how to implement these advanced cleaning workflows to sanitize your datasets efficiently.

Have you ever copied a dataset from a web page, a PDF report, or an enterprise database like SAP or Salesforce into Excel, only to find that your formulas are mysteriously broken? You try to run a VLOOKUP, XLOOKUP, or MATCH, and despite the lookup value looking identical to the source table, Excel stubbornly returns a #N/A error.

More often than not, the culprit is invisible. Your cells are likely infested with non-printable characters. These hidden characters-such as line breaks, carriage returns, null characters, and non-breaking spaces-look like absolutely nothing to the human eye, but to Excel, they are distinct data points that disrupt calculations, sorting, and database matching.

Fortunately, Excel provides a dedicated tool to tackle this exact issue: the CLEAN function. In this comprehensive guide, we will explore how to use the CLEAN formula, understand its limitations, combine it with other powerful functions to create the ultimate data-cleaning formula, and look at alternative methods like Power Query and VBA.

Understanding the Excel CLEAN Function

The CLEAN function was specifically designed to remove low-level computer codes and non-printable characters from imported text. Historically, these characters were used to control teletypewriters, printers, and early computer terminals.

Syntax

=CLEAN(text)

• text: The cell reference or text string from which you want to remove non-printable characters.

How It Works

The CLEAN function removes the first 32 non-printable characters in the 7-bit ASCII set (decimal values 0 through 31). These characters include:

• CHAR(0): Null character
• CHAR(9): Horizontal tab
• CHAR(10): Line feed (often used as a line break in cells)
• CHAR(13): Carriage return

If you have text wrapped onto multiple lines within a single cell, applying CLEAN will strip out those line breaks, collapsing the text back into a single continuous string.

The Dynamic Duo: Combining TRIM and CLEAN

While CLEAN is excellent at removing control codes, it does not remove extra spaces. If your imported data contains both non-printable characters and leading, trailing, or multiple consecutive spaces, CLEAN alone won't solve your lookup errors.

To fix this, Excel professionals pair CLEAN with the TRIM function. The TRIM function removes all leading and trailing spaces, and compresses multiple internal spaces into a single space.

The Formula:

=TRIM(CLEAN(A2))

How It Executes:

1. Excel first processes the inner function: CLEAN(A2). This strips out any ASCII 0–31 control characters and line breaks.
2. Then, the outer function, TRIM(...), takes the cleaned output and strips away any remaining prefix, suffix, or redundant spaces.

This combined formula is the gold standard for basic database preparation. It should be your first line of defense whenever you import new data.

What CLEAN Misses: The Infamous ASCII 160 (Non-Breaking Space)

As helpful as the CLEAN function is, it has a significant blind spot: Unicode and higher-order ASCII characters. Specifically, CLEAN cannot remove character codes above ASCII 31.

The most notorious offender in web-scraped data is ASCII 160, known as the non-breaking space ( ). Web browsers use this character to prevent automatic line breaks between words. To the naked eye, it looks exactly like a standard space (ASCII 32), but Excel treats it as a completely different, printable character. Consequently, neither TRIM nor CLEAN can detect or remove it.

The Ultimate Data Cleansing Formula

To eliminate standard spaces, non-printable control characters, and non-breaking web spaces, you must use the SUBSTITUTE function to convert ASCII 160 characters into standard spaces before running your trim and clean operations.

Use this nested formula to sanitize your data completely:

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

Formula Breakdown:

• CHAR(160) identifies the non-breaking spaces.
• SUBSTITUTE(A2, CHAR(160), " ") replaces every non-breaking space with a standard space (ASCII 32).
• CLEAN(...) removes all control characters (ASCII 0–31).
• TRIM(...) cleans up the newly created standard spaces, removing leading/trailing spaces and collapsing double spaces down to one.

Step-by-Step: How to Detect and Clean Invisible Characters

If you suspect hidden characters are breaking your lookup formulas, you can diagnose and fix the issue using these step-by-step methods.

Step 1: Diagnose with LEN

To verify if hidden characters exist, check the character count of a cell that looks incorrect. If cell A2 contains "Excel" but looks like it has no spaces, write the following formula in an empty cell:

=LEN(A2)

If the result is 6 or 7 instead of 5, you have verified the presence of invisible or non-printable characters.

Step 2: Identify the Rogue Character Code

If you want to know exactly what character is causing the issue, isolate it using the RIGHT, LEFT, or MID functions combined with CODE. For instance, if the trailing character is hidden:

=CODE(RIGHT(A2, 1))

This will return a number. If it returns 160, you have a non-breaking space. If it returns 10, you have a line feed.

Step 3: Apply the Cleaning Formula

Insert a new helper column next to your dirty data and apply the ultimate cleaning formula:

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

Drag the formula down to clean the entire column.

Step 4: Paste as Values

Once your data is cleaned, you should replace the original formulas with static values so you can delete your helper column safely:

1. Select the cleaned range and press Ctrl + C to copy it.
2. Right-click the original data column (or the same column) and choose Paste Special > Values (or press Ctrl + Alt + V, then select Values).
3. You can now delete the helper column. Your lookup formulas should now work flawlessly.

Alternative Method 1: Using Power Query for Bulk Cleaning

If you are dealing with millions of rows or importing data dynamically on a schedule, writing formulas can slow down your workbook. Power Query is Excel's built-in ETL (Extract, Transform, Load) tool, and it handles non-printable characters exceptionally well.

1. Select your data range and go to the Data tab on the Ribbon.
2. Click From Table/Range to open the Power Query Editor.
3. Right-click the header of the column you want to clean.
4. Navigate to Transform > Clean. This will instantly strip out all non-printable ASCII/control characters.
5. To remove extra spaces, right-click the column header again and select Transform > Trim.
6. Go to the Home tab and click Close & Load to return the pristine data to a new Excel sheet.

Alternative Method 2: VBA Macro for One-Click Cleaning

If you regularly receive reports filled with garbage data, you can write a simple macro to clean selected cells instantly without setting up helper columns.

Press Alt + F11 to open the VBA Editor, insert a new module, and paste the following code:

Sub CleanSelectedCells()
    Dim cell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each cell In Selection
        If Not cell.HasFormula Then
            Dim tempVal As String
            tempVal = cell.Value
            ' Replace non-breaking space (ASCII 160) with standard space
            tempVal = Replace(tempVal, Chr(160), " ")
            ' Clean control characters and trim
            cell.Value = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Clean(tempVal))
        End If
    Next cell
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Data cleaning complete!", vbInformation
End Sub

To use this, simply highlight the dirty cells in your worksheet, press Alt + F8, select CleanSelectedCells, and click Run.

Quick Reference: Common Hidden Characters

Conclusion

Non-printable characters can be incredibly frustrating because they hide in plain sight. However, armed with the CLEAN function, you have everything you need to sanitize your data. For basic imports, a simple =TRIM(CLEAN(A2)) will suffice. For web-scraped data or system exports, upgrade to the nested =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) formula to ensure that ASCII 160 non-breaking spaces don't bypass your defenses.

By integrating these data-cleansing habits into your spreadsheet preparation workflow, you will eliminate broken lookups, ensure accurate calculations, and save hours of troubleshooting time.