Data imported into Excel from other systems often contains hidden control characters. These non-printing characters can cause formulas to fail, sorting to behave incorrectly, and data validation to break. The CLEAN function is a built-in Excel tool designed to strip these characters from text. This article explains how to use the CLEAN function to clean your data effectively.
Key Takeaways: Removing Control Characters in Excel
- CLEAN function: Removes the first 32 non-printing control characters in the ASCII code from any text string.
- TRIM and CLEAN together: Use a nested formula to remove both control characters and extra spaces from your data.
- Paste Special > Values: Converts formulas using CLEAN into static, cleaned text you can use elsewhere.
What the CLEAN Function Does and Does Not Remove
The CLEAN function targets non-printing control characters from the early ASCII character set, specifically codes 0 through 31. These characters are often left behind when data is copied from web pages, mainframe systems, or other databases. Common examples include the carriage return (CHAR(13)), line feed (CHAR(10)), and tab (CHAR(9)).
It is important to know the function’s limits. CLEAN does not remove the non-breaking space character (CHAR(160)), which is common in HTML. It also does not remove regular extra spaces between words; for that, you need the TRIM function. The function works on a single text string you provide as its argument.
Characters Removed by the CLEAN Function
The function is designed for a specific technical purpose. It strips out characters used for text formatting and printer commands in older computer systems. This includes characters for starting a new line, moving the print head, or signaling the end of a text file. While these are invisible in a cell, they affect how Excel processes the text in calculations and lookups.
Steps to Use the CLEAN Function in Your Worksheet
You can apply the CLEAN function directly in a cell formula. The basic syntax is =CLEAN(text). You can reference another cell containing the dirty text or type the text directly inside the quotation marks.
- Select the result cell
Click on the cell where you want the cleaned text to appear. This is often a new column next to your original data. - Enter the CLEAN formula
Type =CLEAN( and then click on the cell containing the text with control characters. Close the parenthesis and press Enter. The formula will look like =CLEAN(A2). - Copy the formula down the column
Use the fill handle to drag the formula down and apply it to all cells in your data range. This creates a cleaned version of your entire dataset.
Combining CLEAN with TRIM for Complete Cleaning
For a more thorough clean, nest the CLEAN function inside TRIM. This removes control characters first, then strips leading, trailing, and excessive internal spaces.
- Enter the combined formula
In your result cell, type =TRIM(CLEAN(A2)). Press Enter. - Apply to your data
Drag the fill handle down the column to clean all cells. Your data will now be free of both control characters and irregular spacing.
Converting Formulas to Permanent Cleaned Values
The results from CLEAN are formulas. To replace the original data with the cleaned text, you must convert the formulas to static values.
- Copy the cleaned column
Select all the cells containing your CLEAN or TRIM(CLEAN()) formulas. - Open Paste Special
Right-click on the selected range and choose Paste Special from the context menu. - Paste as values
In the Paste Special dialog box, select Values under the Paste section. Click OK. Your formulas are now replaced with the cleaned text. - Delete the original data
You can now safely delete the original column of uncleaned data if needed.
Common Mistakes and Limitations When Using CLEAN
CLEAN Does Not Remove Non-Breaking Spaces (CHAR 160)
Data from web pages often contains non-breaking spaces, which the CLEAN function ignores. These spaces look like normal spaces but cause errors in formulas like VLOOKUP. To remove them, use the SUBSTITUTE function: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), ” “))). This replaces the non-breaking space with a regular space before cleaning.
Leading Zeros Disappear After Cleaning Numbers
If your data looks like numbers with leading zeros, Excel may convert the cleaned result to a true number, dropping the zeros. To preserve text formatting like leading zeros, first format the result column as Text before pasting values, or use the TEXT function to format the output.
Data Appears Unchanged After Using CLEAN
If the CLEAN function seems to do nothing, your data may not contain the specific control characters it removes. The issue could be extra regular spaces or the non-breaking space character. Test by using the TRIM function alone or the combined SUBSTITUTE formula for CHAR(160).
CLEAN Function vs. Manual and Power Query Methods
| Item | CLEAN Function | Find and Replace | Power Query |
|---|---|---|---|
| Primary Use | Remove ASCII control chars 0-31 | Remove specific known characters | Clean large, recurring datasets |
| Ease of Use | Simple formula, easy to copy | Manual, good for one-time fixes | Requires setup, but reusable |
| Automation | Manual formula copy required | Fully manual process | Fully automated on data refresh |
| Handles Non-Breaking Space | No, requires SUBSTITUTE | Yes, if you search for CHAR(160) | Yes, with built-in transform |
| Best For | Quick cleaning of a single column | Removing a few odd characters | Building a repeatable data cleaning pipeline |
You can now clean imported data by removing disruptive control characters with the CLEAN function. For a complete clean, combine it with TRIM in a nested formula like =TRIM(CLEAN(A2)). Remember to use Paste Special > Values to convert your formulas into permanent cleaned text. For advanced cleaning of web data, incorporate the SUBSTITUTE function to handle non-breaking spaces that CLEAN misses.