TRIM and CLEAN Not Working in Excel? Fix the Hidden Spaces

TRIM and CLEAN can seem broken because they do not remove every character that looks like a space. If the text is in A2, this formula handles ordinary spaces, nonbreaking spaces, and common control characters: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). It returns cleaned text in the formula cell; the original value in A2 stays unchanged.

Why TRIM and CLEAN Sometimes Miss Text

Excel’s TRIM function is narrower than its name suggests. It removes the standard space character, ASCII character 32, from the beginning and end of text and reduces repeated standard spaces between words to one. It does not treat every Unicode whitespace character as an ordinary space. Text copied from websites, PDFs, email, or other systems may contain a different character that looks identical on the worksheet.

A frequent culprit is the nonbreaking space, character 160. It is used to keep words together on a line and often arrives with copied web content. CLEAN has a different job: it removes the first 32 nonprinting ASCII characters, such as many control characters, but it does not remove character 160 or every Unicode character. Combining CLEAN with TRIM therefore does not automatically solve every import problem.

Another common misunderstanding is that these functions edit a cell in place. They do not. A formula creates a cleaned result in another cell. Until you copy that result and paste it as a value over the original, formulas, lookups, and comparisons that refer to the original cell still see the uncleaned text.

What TRIM and CLEAN Actually Do

TRIM is useful for inconsistent spacing made with ordinary spaces. For example, it can turn a text value with extra spaces around and between words into a version with one standard space between each word. It does not remove spaces inside a number or change a number stored as a numeric value. It also does not remove punctuation or normalize capitalization.

CLEAN removes certain nonprinting control characters from text, including characters that may be introduced by data exports. It is not a universal Unicode cleanup tool. In particular, CLEAN does not remove every invisible formatting mark, every nonbreaking space, or every unusual separator used by another application.

They complement each other, but the order and the characters in the source matter. Replacing a special space with a regular one first lets TRIM handle the result. CLEAN can then remove supported control characters, and TRIM can standardize ordinary spacing. If a source contains other Unicode whitespace, you may need to replace that specific character too.

Check the Source Before Changing a Whole Column

Start with one affected cell and compare it with a cell that behaves correctly. Look for clues such as text that appears aligned differently, two entries that look identical but do not match in a lookup, or a filter that separates visually identical labels. A formula bar view can reveal visible spacing, but invisible characters are not always obvious there.

Test the simplest explanation first: use TRIM in a spare cell and see whether the result changes. If it does not, that does not prove the text is clean; it may contain a character TRIM does not recognize. If a lookup is failing, also check whether one entry is a number stored as text and the other is numeric. TRIM and CLEAN do not convert text numbers into numbers.

Keep a copy of the source column before applying a large cleanup.
Test a few different examples, including values with punctuation, accents, or multiple words.
Check whether the issue affects only copied or imported entries; that can point to a source-specific character.
Confirm the exact comparison or lookup problem after cleaning, rather than judging only by appearance.

Use a Formula for Common Imported Spaces

For a value in A2, put the following formula in an empty cell, such as B2. It replaces nonbreaking spaces with ordinary spaces, removes supported control characters, and then normalizes ordinary spacing. If A2 contains the words North and Shore separated by a nonbreaking space, the result is North Shore with one ordinary space.

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

Fill the formula down for the rest of the rows. In Microsoft 365 for Windows, drag the fill handle or double-click it to fill alongside adjacent data. On Mac, the fill handle works as well; keyboard shortcuts can differ, so using the ribbon or fill handle is a straightforward option. Keep the source column until you have checked the results.

If the source contains a narrow no-break space, Unicode U+202F, you can replace that character as well. With the text in A2, this version handles both character 160 and U+202F before cleaning and trimming. For example, it converts either of those separators between North and Shore into a standard space, producing North Shore.

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

Do not add replacements indiscriminately. A special character may be meaningful in a product code, identifier, or language-specific text. Replace only characters you have reason to treat as unwanted whitespace.

Identify a Specific Invisible Character

When the common formula does not help, identify the character rather than adding random substitutions. Excel’s UNICODE function returns the Unicode number for a character. With the suspicious character at the start of A2, this formula returns its code point; if it is a nonbreaking space, the result is 160.

=UNICODE(LEFT(A2,1))

If the character is not at the start, change the position used by MID. For example, with the suspicious character in position 4 of A2, this formula returns that character’s Unicode number. The result depends on the actual character at that position, so inspect a carefully chosen example rather than assuming every row is identical.

=UNICODE(MID(A2,4,1))

Once you know the character, use SUBSTITUTE to replace that character with a normal space or an empty string, depending on whether it separates words or is unwanted entirely. Use UNICHAR with the known Unicode number when the character is outside the ASCII range. A targeted replacement is safer than deleting all spaces, which would turn a label such as North Shore into NorthShore.

Check Formula Results and Replace the Original Values

Cleaning formulas return text, so the result can look correct while the source data remains unchanged. If other formulas still point to the original column, they will not benefit from the cleaned output. Check several rows, including blank cells and examples with punctuation, before replacing anything.

When the output is correct, copy the cleaned cells and paste values into the destination column. In Excel for Microsoft 365 on Windows, use Home > Paste > Values. On Mac, use the Paste Values option on the Home tab as well; the exact keyboard shortcut for Paste Special differs between Windows and Mac, so the ribbon command avoids relying on the wrong shortcut. If you need the original data for auditing, paste values into a new column instead of overwriting it.

After replacing values, test the operation that originally failed. For example, retry the lookup or compare the cleaned labels. If the problem remains, investigate data type, punctuation, spelling, or other invisible characters rather than repeatedly applying TRIM.

Clean Repeated Imports with Power Query

If the same kind of data arrives regularly, Power Query can make cleanup part of the import process. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. In Power Query Editor, select the relevant text column, then use Transform > Format > Trim or Transform > Format > Clean. Choose Close & Load to return the result to Excel.

Power Query’s Trim and Clean operations are useful for their respective whitespace and control-character tasks, but they should not be treated as a guarantee that every Unicode space is removed. If a nonbreaking space remains, use Replace Values in the editor to replace the specific character, or prepare a formula-based cleanup before loading. Check a sample of the loaded result because imported columns can also contain mixed types or inconsistent source formatting.

Power Query is especially useful when you refresh the same kind of file repeatedly: the transformation steps can be applied again on refresh. For a one-time correction, a worksheet formula may be quicker. On Mac, Power Query availability and features can vary by Excel release; if the described editor commands are missing, use worksheet formulas or check the current Excel version’s available Get & Transform features.

Common Reasons the Fix Still Fails

The formula is beside the data: The original cell is unchanged until you paste the formula result as a value over it or update formulas to use the result column.
The character is not a space: A zero-width character, unusual dash, or other Unicode mark may look like spacing but needs a targeted replacement.
The values have different data types: Text “123” and numeric 123 may look alike but are not the same type. TRIM and CLEAN do not perform a general text-to-number conversion.
The wrong column is being cleaned: A lookup may refer to another column or a different workbook range. Check the formula references and the actual lookup values.
Formatting is mistaken for content: Indentation, alignment, or number formats can affect how a value appears without adding spaces to the underlying text.
Only some rows have the character: Mixed exports can contain ordinary spaces in most rows and special spaces in a few. Test several affected records before choosing a cleanup rule.

Frequently Asked Questions

Why does TRIM not remove a nonbreaking space in Excel?
Because a nonbreaking space is character 160, not the ordinary space character TRIM is designed to handle. Replace it with SUBSTITUTE and CHAR(160), then apply TRIM to standardize the resulting spaces.

Does CLEAN remove all invisible characters?
No. CLEAN removes the first 32 nonprinting ASCII control characters, not every invisible or Unicode character. Identify unusual characters and replace them specifically if they are unwanted.

Can I use TRIM and CLEAN on a whole column?
Yes. Enter a formula for the first row of data and fill it down. Review the results before copying and pasting values, especially if some entries contain meaningful spacing or special characters.

Why does my lookup still fail after cleaning?
The lookup may still reference the original cells, or the mismatch may be a different issue such as text versus number, a spelling difference, or punctuation. Confirm that the lookup uses the cleaned output and check the data types on both sides.

Will TRIM remove spaces between words?
It reduces repeated ordinary spaces between words to one; it does not remove every single word-separating space. If you remove all spaces with a replacement, words may run together, so use that only when spaces are not meaningful.

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.