TRIM Excel Not Working? Fix Spaces That TRIM Misses

If TRIM appears not to work in Excel, the text may contain a character that TRIM does not recognize as a regular space. For ordinary spaces, with the text in A2, enter =TRIM(A2) in another cell; if the problem is a nonbreaking space, replace it with a regular space first, then apply TRIM.

What Excel TRIM does—and what it does not

Excel’s TRIM function cleans up the ordinary space character (character code 32). It removes spaces at the start and end of text and reduces repeated spaces between words to one. It does not remove every character that looks like a blank, and it does not remove every kind of invisible character.

For example, with the text North Bay in A2, =TRIM(A2) returns North Bay: the leading and trailing spaces disappear, and the three spaces between the words become one. That behavior is useful for names, labels, and imported text, but it can be surprising if the spacing is intentional. TRIM does not preserve multiple ordinary spaces inside a value.

TRIM also does not change the original cell. Its result appears wherever you put the formula, so the source stays available for comparison. If you need to replace the source with the cleaned results, copy the formula results and use Paste Special > Values.

Check whether the spaces are ordinary spaces

A visible gap is not proof that the character is an ordinary space. Text copied from a website, PDF, email, or another application can contain a nonbreaking space. It looks much like a regular space but has a different character code, so TRIM leaves it alone. Tabs and line breaks can also arrive with pasted or imported data.

One quick check is to compare the source with a cleaned result, then inspect the source cell in the formula bar. A character that appears blank can be difficult to identify by eye. Another clue is whether the text came from an external source or whether a search, sort, or match behaves differently for values that look identical. Do not assume that a failed TRIM formula means Excel is malfunctioning; the input may contain a different character.

Use a separate result column while diagnosing the issue. For example, with source text in A2, enter =TRIM(A2) in B2 and compare the two values. If the unwanted gap remains in B2, test for nonbreaking spaces or other hidden characters rather than repeatedly applying TRIM.

Remove nonbreaking spaces

The common fix for nonbreaking spaces is to substitute ordinary spaces for them before calling TRIM. Excel’s CHAR(160) returns the nonbreaking space used in many imported text strings. With the affected text in A2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). If A2 contains nonbreaking spaces around and between the words North and Bay, this returns North Bay.

SUBSTITUTE changes each matching character to a regular space; TRIM then removes the extra spaces at the edges and collapses repeated ordinary spaces. The two functions do different jobs, which is why TRIM alone may leave the original problem untouched. If the data has both ordinary and nonbreaking spaces, this combination handles both.

Some sources use other Unicode spaces. For a narrow nonbreaking space (Unicode 8239), with the text in A2, use =TRIM(SUBSTITUTE(A2,UNICHAR(8239)," ")). This returns North Bay if the words in A2 are separated by that character. UNICHAR converts the Unicode number to a character; substitute the code for the specific character you have identified, rather than assuming every unusual blank has the same code.

Handle tabs and line breaks in imported text

Tabs and line breaks are not ordinary spaces either. A tab can make text look unusually far apart, while a line break can make a cell display on more than one line. TRIM does not remove either. You can replace common ASCII tab, line-feed, and carriage-return characters with ordinary spaces before trimming.

With the affected value in A2, use =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(9)," "),CHAR(10)," "),CHAR(13)," ")). This replaces nonbreaking spaces, tabs, line feeds, and carriage returns with regular spaces, then normalizes spacing. If A2 contains North, a tab, and Bay, the result is North Bay.

Replacing control characters with spaces is often preferable to deleting them when they separate words. Excel’s CLEAN function removes many nonprinting ASCII control characters, including common tabs and line breaks, but deleting a separator between two words could join them. For instance, removing a tab from North[TAB]Bay without replacing it with a space could produce NorthBay. Inspect the source and choose whether a particular character should become a space or disappear.

Rule out formula and calculation problems

Sometimes the character is not the issue: Excel may not be calculating or displaying the formula as expected. Check these common causes before changing the cleanup formula:

The formula is displayed as text. If the cell is formatted as Text, or the entry begins with an apostrophe, Excel may show =TRIM(A2) instead of calculating it. Change the cell format to General, then re-enter the formula. You can select the cell, press F2, and press Enter.
Show Formulas is on. Excel then displays formulas in cells instead of their results. On Windows, press Ctrl+` to toggle the view, or use Formulas > Show Formulas. The displayed formula is not evidence that TRIM failed.
Calculation is set to Manual. Choose Formulas > Calculation Options > Automatic, then recalculate if needed. A formula that has not recalculated may show an old result.
The formula refers to the wrong cell. Verify that the reference points to the actual source text, not a blank cell or a different row. When filling a formula down, check that each row’s reference moves as intended.
The output is being judged by appearance alone. A cell’s formatting, column width, or wrapping can affect how text looks. Compare the actual formula result and source, not just the visible gap on screen.

On Mac, the ribbon labels and locations can differ slightly by Excel release, but the same formula and underlying TRIM behavior apply. Use the Formulas tab to inspect formula display and calculation settings. Excel for the web also supports TRIM; if a workbook behaves differently, check its source characters and calculation state rather than assuming the function has a different definition.

Use Find and Replace for a one-time cleanup

For a small range that contains one known unwanted character, Find and Replace can be quicker than adding a formula column. On Windows, select the range and choose Home > Find & Select > Replace, or press Ctrl+H. Enter the character to find and a regular space as the replacement, then review the affected cells before continuing. For a Mac, use Edit > Find > Replace; the shortcut may differ by keyboard and Excel version.

Finding a nonbreaking space can be awkward because it is invisible. One practical approach is to copy the character from a known affected cell and paste it into the Find what box, then enter a regular space in Replace with. If you cannot identify the character reliably, a helper formula using CHAR(160) is easier to inspect and repeat. Find and Replace edits selected source cells directly, so keep a copy of the original data if the change is difficult to reverse.

Choose a cleanup method that fits the data

A formula is usually the safer choice when the source needs to remain unchanged, when new rows will arrive, or when you want to review results before replacing the original. Fill the formula down alongside the source, check representative results, and only then copy and paste values if a fixed cleaned column is required.

For repeated imports, Power Query can provide a refreshable cleanup workflow. In Excel for Microsoft 365 on Windows, load the data into Power Query and use the text-formatting options, such as Transform > Format > Trim, on the relevant column. Because source data can contain different kinds of whitespace, inspect a sample after the transformation; the behavior of a particular cleanup step should not be assumed to cover every Unicode character. A formula with an explicit SUBSTITUTE is more transparent when you know the exact character that must be replaced.

TRIM is not a universal text sanitizer. It does not convert numbers stored as text into numbers, correct spelling, remove every punctuation mark, or guarantee that two visually similar strings are identical. Treat spacing cleanup as one step in data preparation, then separately check the data type and any other characters relevant to your task.

Check whether the cleaned text now matches

If the reason for cleaning is that a lookup or comparison fails, test the cleaned values directly. With a source value in A2 and another value in B2, use =A2=B2 in C2 to see whether Excel considers the two cell contents equal; the result is TRUE or FALSE. If the comparison is FALSE even though the values look the same, there may be another hidden character, a different punctuation mark, or a number-versus-text mismatch.

For a fair test, apply the same cleanup logic to both sides of a comparison. Cleaning only one value can leave the other with a nonbreaking space or extra ordinary spaces. Also remember that TRIM reduces repeated spaces to one rather than removing every internal space. That is generally right for names and sentences, but it may not be right for fixed-width codes, intentionally spaced labels, or text where exact spacing carries meaning.

Frequently Asked Questions

Why does TRIM not remove spaces copied from a website?
Copied text may contain nonbreaking spaces or other Unicode whitespace instead of ordinary spaces. For a common nonbreaking space, with the text in A2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Other characters may require a different character code.

Does Excel TRIM remove spaces between words?
It reduces repeated ordinary spaces between words to one; it does not remove every single space between words. It also removes ordinary spaces at the beginning and end of the text.

How do I remove a tab or line break with TRIM?
TRIM alone does not remove tabs or line breaks. Replace the relevant character with a regular space first, then use TRIM. CHAR(9), CHAR(10), and CHAR(13) represent common tab, line-feed, and carriage-return characters.

Can TRIM clean the original cell in place?
No. A formula returns a result in its own cell and leaves the source unchanged. To overwrite the source, copy the reviewed results and use Paste Special > Values, preferably after keeping a backup of the original data.

Why does my formula appear in the cell instead of the result?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell to General and re-enter the formula, and check the Show Formulas setting.

Will TRIM fix a failed lookup?
It can fix a lookup problem caused by extra ordinary spaces, but it will not fix every mismatch. Nonbreaking spaces, other hidden characters, different punctuation, and text-versus-number differences can still prevent a match.

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.