Excel CLEAN Function Not Working? Fix Hidden Characters

If Excel’s CLEAN function seems not to work, the usual reason is that the character is not one CLEAN removes: it removes the first 32 nonprinting ASCII characters, but leaves things such as nonbreaking spaces and many Unicode characters untouched. If the text is in A2, start with =CLEAN(A2); if that does not fix the problem, identify the character and remove or replace it specifically.

What Excel CLEAN Removes

CLEAN takes text and deletes certain control characters, including common tab and line-break characters that can arrive in copied or imported data. These characters are often invisible in a worksheet, so a value may look normal while behaving differently in a comparison, lookup, or calculation. CLEAN can make the text easier to use, but it is not a universal text sanitizer.

For example, suppose A2 contains the word Lake, followed by a line-feed character, followed by View. With that text in A2, =CLEAN(A2) returns LakeView because CLEAN deletes the line feed rather than replacing it with a space. That result can be surprising when the line break separates two words. If the break should become a space, replace it before cleaning, as described below.

CLEAN also does not remove ordinary spaces. A leading or trailing space may therefore remain, and multiple spaces between words will not be reduced. Excel’s TRIM function handles ordinary spaces by removing leading and trailing spaces and reducing repeated internal spaces to one. It does not, by itself, remove every kind of invisible spacing character.

Check What Is Actually in the Cell

First check whether the apparent problem is in the source value or in the way Excel displays it. Click the cell and look in the formula bar; then test a cleaned result in a separate cell so the original data remains available. If the values are in a table, check a few affected rows rather than assuming every row contains the same character.

A useful clue is how the text fails. If a lookup does not match, compare the displayed strings and check for extra spaces or different Unicode characters. If a cell contains two words that run together after CLEAN, a line break was probably deleted. If CLEAN leaves a visible gap or a value still fails to match, the remaining character may be a nonbreaking space or another character outside CLEAN’s range.

With the suspect text in A2, =LEN(A2) returns its character count, including spaces and many invisible characters. Compare that count with a clean-looking example in another cell. A difference is evidence that the strings are not identical, although it does not identify the particular character on its own.

To inspect a character at a known position, put the text in A2 and the character position number in B1; =UNICODE(MID(A2,B1,1)) returns the Unicode code point for that character. Adjust B1 to inspect another position. This is most useful when you have narrowed the issue to a particular spot; for text with many hidden characters, checking positions one by one can be tedious.

Nonbreaking Spaces Are a Common Cause

One frequent reason CLEAN appears ineffective is a nonbreaking space. It can look like a regular space but is a different character, commonly found in text copied from websites, PDFs, or other systems. In Excel it is often represented by CHAR(160). CLEAN does not remove it, and TRIM does not treat it as an ordinary space.

If the value is in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),CHAR(32)))) replaces nonbreaking spaces with ordinary spaces, removes CLEAN’s control characters, and then trims ordinary spaces. For example, a value consisting of North, a nonbreaking space, and Branch becomes North Branch. This is often more useful than deleting the nonbreaking space outright because it preserves the separation between words.

The order matters: SUBSTITUTE changes the special space into a regular one, CLEAN removes its supported control characters, and TRIM tidies ordinary spaces. If a string has other unusual Unicode spacing characters, this formula will not necessarily catch them. It addresses CHAR(160), not every possible character that may look like a space.

Replace Line Breaks Instead of Deleting Them

CLEAN deletes line breaks; it does not know whether a break is formatting or a meaningful word separator. If a line break between words should become a space, replace the line feed first. With the original text in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10),CHAR(32)))) changes line feeds to ordinary spaces, removes other supported control characters, and trims the result.

For example, if A2 contains North, a line break, and Branch, the formula returns North Branch rather than NorthBranch. Many cells created with an in-cell line break use CHAR(10). Imported text can contain a carriage return as well; CLEAN removes supported carriage-return control characters, while replacing CHAR(10) before cleaning preserves a space where that line feed occurred.

Do not use this replacement automatically if line breaks carry meaning, such as separate lines in an address or list. In those cases, decide whether to preserve the break, convert it to a delimiter, or split the content into separate cells. A blanket cleanup formula can change the structure of the text even when it solves a matching problem.

Remove a Specific Unicode Character

Some invisible characters are Unicode characters that CLEAN does not cover, including zero-width characters that may be introduced by web content or data exports. When you know the code point, SUBSTITUTE and UNICHAR can target it. For text in A2, =SUBSTITUTE(A2,UNICHAR(8203),"") removes zero-width space U+200B (decimal code point 8203).

This is a targeted fix, not a general rule for all hidden characters. Another file may contain a different character, and deleting a character without checking its role can alter legitimate text. If the character should separate words, replace it with CHAR(32) rather than an empty string. Keep the original column until you have checked the cleaned output against representative rows.

UNICODE and UNICHAR are useful for diagnosis and targeted replacements in current Excel versions, but an unfamiliar code point can be hard to interpret without context. If the source system or export process is known, its documentation may explain why a particular character appears. When it is not, inspect several affected examples and avoid applying an unverified replacement across important records.

Use a Helper Column and Verify the Result

For a dataset, put the cleanup formula in a helper column instead of overwriting the source. Enter the formula for the first row, fill it down, and review the results. In Excel for Microsoft 365 on Windows, the fill handle at the bottom-right corner of the selected cell can be dragged down; double-clicking it often fills alongside a neighboring populated column. Copy the results and use Paste Special > Values if you need fixed text rather than formulas.

Check more than whether the output looks right. Try the cleaned values in the lookup, comparison, or import step that originally failed. Also inspect edge cases: names with hyphens, intentional multiple lines, accented letters, or punctuation should remain intact. If some values are fixed and others are not, the source may contain more than one kind of unwanted character.

When comparing two strings, remember that the problem may be in either one. Apply the same appropriate cleanup to both sides of a comparison or lookup, while preserving the source data. Cleaning only the lookup value will not help if the corresponding table value contains the hidden character.

Excel Version, Locale, and Related Misconceptions

CLEAN is a long-standing Excel function and is available in Microsoft 365 for Windows, as well as Excel for Mac and Excel for the web. The formulas described here work across those platforms in typical installations. Excel’s interface can differ slightly between desktop and web, but the core issue is the character set CLEAN handles, not a Windows-only limitation.

In some regional settings, Excel expects semicolons rather than commas between function arguments. If a formula with commas produces a syntax error, your list-separator setting may be the reason; use the separator Excel expects in that installation. The examples here use the comma-separated syntax specified for this article.

A related misconception is that CLEAN removes all formatting or all nonprinting text. It does not clear cell formatting, change a number stored as text into a number, or remove every invisible Unicode character. It changes the text result according to its limited control-character behavior. If the actual issue is a number stored as text, a date interpreted incorrectly, or an unwanted cell format, investigate that specific issue rather than expecting CLEAN to fix it.

Frequently Asked Questions

Why does CLEAN not remove spaces in Excel?
CLEAN removes supported nonprinting ASCII control characters, not ordinary spaces. Use TRIM for leading, trailing, or repeated regular spaces. If the gap is a nonbreaking space, replace CHAR(160) with an ordinary space first.

Does CLEAN remove line breaks?
Yes, it removes supported line-break control characters. That can join words together. If a line break should become a word separator, substitute an ordinary space for the line feed before applying CLEAN.

Why does CLEAN work in one row but not another?
The rows may contain different hidden characters. One might have a control character that CLEAN removes, while another has a nonbreaking space or a Unicode character outside CLEAN’s range. Inspect and test affected rows individually before choosing a replacement.

Does TRIM remove nonbreaking spaces?
Not reliably as a nonbreaking-space cleanup method. TRIM is for ordinary spaces. Replace CHAR(160) with CHAR(32), then use TRIM if you want to normalize ordinary spacing.

Can I use CLEAN directly over my original data?
It is safer to calculate cleaned text in a helper column and verify it before replacing anything. CLEAN may remove line breaks that carry meaning, and a formula cannot be undone as an edit to the original values if you have already pasted over them.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.