OmniDocWhat the CLEAN Function Does in Excel and How to Use It
Learn what Excel's CLEAN function removes, how to pair it with TRIM, check results with LEN, and find the function name in other Excel languages.
If you have ever pasted data from a website, an export file, or a database and found that lookups fail for no visible reason, the cause is often a hidden character. The text looks normal, but a line break, a tab, or another control character sits inside the cell. The excel clean data function, called CLEAN, is built for exactly this problem. It strips out nonprintable characters so the text behaves the way you expect.
This article is for anyone who works with imported spreadsheet data and needs a reliable way to tidy it. You will learn what CLEAN removes and what it misses, how to combine it with TRIM, how to check that the formula did its job, and what the function is called in other Excel languages. The method works in any spreadsheet program that supports the same text functions.
What does the Excel clean data function remove? The short answer
CLEAN removes the nonprintable characters in a text string, which are the control codes numbered 0 through 31, and leaves the printable text intact. Pair it with TRIM when you also need to remove extra spaces.
| Your situation | What to do | Why |
|---|---|---|
| Pasted text has line breaks or tabs inside cells | Use =CLEAN(A2) in a helper column | CLEAN removes codes 0 through 31, which include line feeds and tabs |
| Lookups fail on values that look identical | Use =TRIM(CLEAN(A2)) | Hidden control characters and doubled spaces both break exact matches |
| Cells contain non-breaking spaces from a web page | Use SUBSTITUTE with CHAR(160) after CLEAN | CLEAN does not remove character 160 |
| You need to confirm the formula changed something | Compare LEN of the original and cleaned cells | A difference in length shows how many characters were removed |
What CLEAN removes and what it leaves behind
CLEAN works on one text value at a time. It scans the string and deletes any character whose code falls in the nonprintable range, 0 through 31. These codes include carriage returns, line feeds, and tabs. They often appear when data is copied from a PDF, a web page, a text file, or an export from another system.
The function does not change letters, numbers, punctuation, or normal spaces. It also does not fix capitalization or remove leading and trailing spaces. Those jobs belong to other functions.
Characters CLEAN misses
Some characters that look like spaces fall outside the 0 to 31 range. A common one is the non-breaking space, which has code 160. Web pages and some accounting exports use it heavily. After CLEAN runs, these cells still fail exact matches.
To handle this, wrap the text in SUBSTITUTE to replace CHAR(160) with a regular space, then pass the result through TRIM. A combined version looks like this: =TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," ")). Test it on a few rows before you fill it down the whole column.
Function names in other languages
The function name changes with the language of Excel. Your formulas must use the local name, or Excel will show an error. Common examples include:
- French: NETOYAGE, so the formula is =NETOYAGE(A2)
- Spanish: LIMPIAR
- German: SÄUBERN
If you are unsure, type the first letters of the function in a cell and check the autocomplete list. Shared workbooks may also open with the English name in one version and the local name in another, so confirm the name on the machine where the file will be used.
How CLEAN and TRIM work together
CLEAN and TRIM solve different problems, and each one leaves gaps the other covers. CLEAN removes invisible control characters but keeps every regular space. TRIM removes leading and trailing spaces and reduces runs of internal spaces to a single space, but it does not touch control characters.
When you nest them as =TRIM(CLEAN(A2)), CLEAN runs first and removes the hidden characters. TRIM then tidies the spacing that remains. This order matters. If a control character sits between two spaces, TRIM may not see the extra spacing until CLEAN removes the character.
Use a helper column for the cleaned values. Keep the original column until you have checked the results. Once you are satisfied, copy the helper column and paste it as values over the original.
Checking that the cleaning worked
A formula that returns clean-looking text does not prove that the hidden characters are gone. Two quick checks help you confirm the result.
- Compare lengths. In a new column, enter =LEN(A2)-LEN(CLEAN(A2)). A value above zero means CLEAN removed characters from that cell. A zero means the cell had none in the range CLEAN covers.
- Test a lookup. Match the cleaned value against a known good value with an exact-match formula. If the match now succeeds, the hidden characters were likely the problem.
If the length difference is zero but lookups still fail, check for non-breaking spaces with the SUBSTITUTE approach described above.
📊 At a glance
| Function | What it removes | When to use it |
|---|---|---|
| CLEAN | Codes 0 through 31, such as line breaks and tabs | Imported text with invisible control characters |
| TRIM | Leading, trailing, and repeated regular spaces | Text with uneven spacing |
| SUBSTITUTE with CHAR(160) | Non-breaking spaces | Web or accounting exports that break exact matches |
Putting it to use
- Open the spreadsheet and find a column of imported text that causes lookup or sorting problems.
- In an empty helper column, enter =TRIM(CLEAN(A2)), adjusting the cell reference to match your data.
- Fill the formula down to the last row of data.
- In another helper column, enter =LEN(A2)-LEN(CLEAN(A2)) to see which rows had control characters.
- Check a few rows where the length difference is zero but the data still looks wrong. If you find non-breaking spaces, add SUBSTITUTE with CHAR(160) inside the formula.
- When the results look correct, copy the helper column and paste it as values over the original column.
- Save the file. If you want to keep editing the cleaned sheet in the OmniDoc desktop client, open it in UniCell, the spreadsheet editor included with the client, which is available at download.omnidoc.top.
❓ FAQ
1. Does CLEAN remove extra spaces?
No. CLEAN removes only nonprintable characters with codes 0 through 31. Use TRIM to remove leading, trailing, and repeated spaces.
2. Why does my cell still fail a lookup after using CLEAN?
The cell may contain a non-breaking space, which has code 160 and is outside CLEAN's range. Replace it with SUBSTITUTE before you run TRIM.
3. Can CLEAN change the formatting of a cell?
CLEAN returns new text and does not change number formats, fonts, or colors. Check the formatting separately if the cell needs it.
4. Should I clean data before or after importing it?
Clean the data after import, in a helper column, so you can compare the original and cleaned values. Keep the original until the results are verified.
📝 Summary
The excel clean data function, CLEAN, removes nonprintable control characters from text, and TRIM handles the spacing that remains. Confirm the result by comparing lengths, and add a SUBSTITUTE step when non-breaking spaces are present. Use the local function name in your Excel language, then keep working on the cleaned sheet in UniCell if you use the OmniDoc desktop client.
