Text imported into Excel can look normal and still fail in a lookup, filter, or export. A tab or line break may be hidden inside the cell. The CLEAN function removes many common non-printable characters, but it does not remove every Unicode character.
Use =CLEAN(A2) in a helper column to remove ASCII control characters 0–31 from the text in A2. If the problem is an extra space, use TRIM. If the text contains a nonbreaking space, replace that character with SUBSTITUTE first.
Free example workbook
Use the same CLEAN, TRIM, SUBSTITUTE, and Unicode examples from this guide in Excel.
What does the CLEAN function do in Excel?
CLEAN returns text after removing the first 32 non-printing characters in the 7-bit ASCII code, values 0 through 31. This includes common control characters such as tabs and line feeds. Microsoft also notes that CLEAN does not remove several additional non-printing Unicode characters by itself.
The syntax is:
=CLEAN(text)text is the cell or text value you want to clean. For example, =CLEAN(A2) returns a cleaned version of A2 without changing the original cell.
Basic CLEAN function example
| Original text | Formula | Result |
|---|---|---|
| ACME[tab]SUPPLIES | =CLEAN(A2) |
ACMESUPPLIES |
| West[line feed]Office | =CLEAN(A3) |
WestOffice |
| North[nonbreaking space]Ridge | =CLEAN(A4) |
North[nonbreaking space]Ridge |
The tab and line feed are removed. The nonbreaking space, character 160, remains.
If you want to create a test value yourself, enter:
="ACME"&CHAR(9)&"SUPPLIES"Then clean it with:
=CLEAN(A2)How to use CLEAN in Excel
- Keep an unchanged copy of the source data. This gives you something to compare against if CLEAN changes text you wanted to keep.
- Add a blank helper column. Do not overwrite the source immediately.
- Enter the formula. In the first helper row, use
=CLEAN(A2). - Fill the formula down. Apply it to the rest of the imported records.
- Review the results. Check rows that contained line breaks, tabs, unusual spaces, or text used as lookup keys.
- Replace formulas with values only if needed. Use Paste Special → Values after you have checked the output.
Keep the original column until you have tested lookups, filters, exports, or any other process that depends on the cleaned text.
CLEAN vs. TRIM in Excel
CLEAN and TRIM solve different problems.
| Function | What it removes | What it does not solve |
|---|---|---|
CLEAN |
ASCII control characters 0–31 | Extra ordinary spaces and many Unicode control characters |
TRIM |
Leading and trailing regular spaces, plus repeated regular spaces between words | Nonbreaking spaces such as character 160 |
TRIM is designed around the regular ASCII space, character 32. A space copied from a website may look identical but actually be a nonbreaking space, character 160.
Use CLEAN and TRIM together
If imported text contains both control characters and unnecessary regular spaces, combine the two functions:
=TRIM(CLEAN(A2))For example, a value with extra spaces before North, a hidden tab, and several spaces before Ridge Services can be reduced to:
North Ridge Services
CLEAN removes the control character first. TRIM then handles the regular spaces. This combination still does not remove a nonbreaking space.
When CLEAN does not remove the character
Microsoft notes that CLEAN was designed for ASCII characters 0–31 and does not remove every additional Unicode non-printing character. Its documentation specifically identifies values 127, 129, 141, 143, 144, and 157 as additional characters CLEAN does not remove by itself.
A common problem is the nonbreaking space, character 160. To replace it with a normal space before cleaning and trimming, use:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
If you know the Unicode character you want to remove, you can target it directly. For example:
=SUBSTITUTE(CLEAN(A2),UNICHAR(127),"")If you need to inspect a suspicious character at a known position, UNICODE can return its code number:
=UNICODE(MID(A2,5,1))Practical examples
Text imported from CSV files or software
Start with =CLEAN(A2) when a field contains tabs, line feeds, or other low ASCII control characters.
Text copied from websites
If CLEAN appears to work but an unusual space remains, try:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))CRM or database keys
If client IDs or other keys fail exact matches, clean both the lookup value and the source-key column. Cleaning only one side can leave the mismatch in place.
PDF-to-Excel conversion
CLEAN can remove some hidden control characters after conversion. Review the remaining spaces and line structure because CLEAN does not fix every PDF import issue.
Failed XLOOKUP or exact matches
Two cells can look identical while one contains a hidden character. Clean both comparison values, then test the lookup again.
Common CLEAN function mistakes
- Expecting CLEAN to remove extra spaces. Use TRIM for regular spacing problems.
- Expecting CLEAN to remove every Unicode character. It does not. Use SUBSTITUTE when you know the character.
- Replacing the source before checking the result. Keep the original data until the cleaned version is verified.
- Cleaning only one side of a lookup. Hidden characters can exist in either value.
- Assuming removed line breaks should always join words. Sometimes the line break should become a normal space instead.
If a line feed should become a space rather than disappear, use:
=SUBSTITUTE(A2,CHAR(10)," ")CLEAN function vs. Power Query
A helper formula is usually enough for a one-time cleanup or a small number of rows. If the same CSV or database export arrives repeatedly, Power Query can save transformation steps and run them again when you refresh the query. Use formulas when you want a quick, visible result beside the source. Use Power Query when the import and cleanup steps repeat.
For a broader cleanup workflow, see How to Clean Data in Excel.
Need help cleaning a business spreadsheet?
Simple Office AI helps small businesses clean and organize Excel files and recurring admin data. Do not send sensitive client data before agreeing on a safe way to share the file.
Frequently asked questions about Excel CLEAN
What does CLEAN do in Excel?
CLEAN removes the first 32 non-printing ASCII characters, values 0–31, from text. It is useful for data imported from software, text files, databases, and other external sources.
Does CLEAN remove all non-printable characters?
No. Microsoft notes that CLEAN does not remove several additional Unicode non-printing characters by itself. A nonbreaking space, character 160, also needs separate handling.
What is the difference between CLEAN and TRIM?
CLEAN targets control characters. TRIM removes leading and trailing regular spaces and reduces repeated regular spaces between words.
Why does CLEAN not remove a space copied from a website?
The character may be a nonbreaking space rather than a normal space. Try replacing CHAR(160) with a regular space using SUBSTITUTE before CLEAN and TRIM.
Can I use CLEAN and TRIM together?
Yes. =TRIM(CLEAN(A2)) is useful when imported text contains both control characters and unnecessary regular spaces.
How do I remove a specific Unicode character in Excel?
If you know the code, target it with SUBSTITUTE. For example, =SUBSTITUTE(A2,UNICHAR(127),"") removes Unicode character 127 from the text in A2.