TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)

Quick answer: TRIM only removes the ordinary space character (code 32). If spaces survive TRIM, they are usually non-breaking spaces (code 160), which come from web pages, PDFs, emails and many exports. Convert them to normal spaces first, then trim:

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

If that still leaves something behind, the culprit is another invisible character (a line break, a tab, a narrow or zero-width space). Step 1 below shows you how to identify it in seconds, and Step 3 gives a formula that handles the common characters listed below.

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Excel for Microsoft 365, 2024, 2021, 2019, 2016 (Windows, Mac, web): TRIM, SUBSTITUTE, CLEAN, UNICHAR and UNICODE work in all of them. The one-cell “X-ray” formula in Step 1 needs TEXTJOIN and SEQUENCE (Excel 365/2021+). An alternative for older versions is included.
Google Sheets: Same problem, same functions. Google’s own TRIM page says non-breaking spaces are not trimmed. Sheets-specific options are below.

Why TRIM ignores some spaces

Microsoft’s data-cleaning guide is specific: TRIM removes the 7-bit ASCII space (value 32), CLEAN removes nonprinting characters 0–31, and for higher characters, including 160, you’re expected to use SUBSTITUTE to replace them with something TRIM and CLEAN understand. So TRIM isn’t broken. It’s doing exactly its job, and the character in your cell isn’t the one it handles.

A non-breaking space looks identical to a normal space on screen. Web pages use it ( ) to keep words together, so it comes along whenever you copy from a browser, a PDF or an HTML email.

Step 1: Find out which character it is

Don’t guess. List the code of every character in the cell. In Excel 365, Excel 2021 or Google Sheets:

=TEXTJOIN(",",TRUE,UNICODE(MID(A2,SEQUENCE(LEN(A2)),1)))

For a cell containing SKU-101 followed by a non-breaking space, this returns:

83,75,85,45,49,48,49,160

The last code, 160, is the problem. In Google Sheets, ARRAYFORMULA is required for this X-ray pattern: =ARRAYFORMULA(TEXTJOIN(",",TRUE,UNICODE(MID(A2,SEQUENCE(LEN(A2)),1)))). In the live check, the unwrapped version returned only 32; the wrapped version returned 32,65,160,32.

Excel 2019 and earlier: put the numbers 1, 2, 3 … across B1:T1 and this in B2, then fill right:

=IFERROR(UNICODE(MID($A2,B$1,1)),"")

What the codes mean

Code	Character	Typical source	Removed by TRIM?	Removed by CLEAN?
32	Normal space	Typing	✅	❌
160	Non-breaking space	Web pages, PDFs, HTML email, some ERP exports	❌	❌
8239	Narrow no-break space	Number formatting in some locales (e.g. “1 234”)	❌	❌
8201 / 8199	Thin space / figure space	Typeset documents	❌	❌
12288	Full-width (ideographic) space	Chinese/Japanese/Korean input	❌	❌
9	Tab	Text files, copied tables	❌	✅
10 / 13	Line feed / carriage return	Alt+Enter, multi-line exports	❌	✅
8203	Zero-width space	Web pages, chat apps	❌	❌
65279	Zero-width no-break space (BOM)	Start of some UTF-8 files	❌	❌

The TRIM and CLEAN columns follow Microsoft’s documentation (TRIM handles 32, CLEAN handles 0–31). The 8239/12288/8203 rows were also checked in LibreOffice: TRIM left them all in place.

Step 2: Remove non-breaking spaces (the usual fix)

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

Replace 160 with a space, not with nothing. Between two words, a non-breaking space is a real separator. Delete it and “New York” becomes “NewYork”. TRIM then removes any leftovers at the ends and collapses doubles.

CHAR(160) or UNICHAR(160)? On Excel for Windows both work. But CHAR depends on the platform’s character set: Microsoft’s CODE documentation says Mac uses the Macintosh character set. In our LibreOffice check, SUBSTITUTE(A2,CHAR(160),"") did not remove the non-breaking space, while UNICHAR(160) did. UNICHAR is the safe choice when a file is shared across platforms.

Make it permanent: put the formula in a helper column, fill it down, copy, then Paste Special > Values over the original column and delete the helper. Until you do this, lookups and pivot tables still read the original, dirty column.

Step 3: Remove common invisible spaces in one formula

When the X-ray shows a mix of characters, nest the replacements. Spaces become normal spaces, line breaks and tabs become spaces, and zero-width characters are deleted:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," "),UNICHAR(8239)," "),UNICHAR(8201)," "),UNICHAR(12288)," "),CHAR(10)," "),CHAR(9)," "),UNICHAR(8203),""),UNICHAR(65279),"")))

In LibreOffice, a test string containing a leading non-breaking space, a narrow no-break space, a thin space, an ideographic space, a zero-width space and a line break came out as clean single-spaced text.

Why replace line breaks before CLEAN? CLEAN deletes codes 0–31, so Line1 + line feed + Line2 becomes Line1Line2. Replacing CHAR(10) with a space first keeps the words apart.

In Excel 365 you can make this easier to maintain with LET. The list of codes sits in one place:

=LET(t,A2,
 step1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,UNICHAR(160)," "),UNICHAR(8239)," "),UNICHAR(8201)," "),UNICHAR(12288)," "),CHAR(10)," "),CHAR(9)," "),
 step2,SUBSTITUTE(SUBSTITUTE(step1,UNICHAR(8203),""),UNICHAR(65279),""),
 TRIM(CLEAN(step2)))

Step 4: TRIM “worked” but the problem is still there

If the X-ray shows only normal characters and things still don’t match, the problem isn’t spaces:

The formula result is text, and you need a number

=TRIM(" 123 ") returns the text “123”. In LibreOffice, ISTEXT(TRIM(A2)) returned TRUE for a padded number. SUM ignores it and lookups against real numbers fail. Convert while cleaning:

=--TRIM(SUBSTITUTE(A2,UNICHAR(160),""))

Here 160 is replaced with nothing, because a space inside a number (“1 234”) would stop the conversion. More methods: convert numbers stored as text.

You cleaned a copy, not the original

Your TRIM column is clean, but XLOOKUP, COUNTIF or the pivot table still point at the original column. Either repoint them or paste the clean values over the original (Step 2).

Calculation is set to Manual

If a new TRIM formula shows the old value until you press F9, check Formulas > Calculation Options and set it to Automatic.

The difference is case, or a look-alike character

A Latin “A” and a Cyrillic U+0410 look the same but have different codes. The X-ray formula shows that too: compare the codes of the two cells side by side.

For the full list of reasons a lookup fails on “identical” values, see XLOOKUP returns #N/A even though the value exists.

Fix it without formulas: Find and Replace

Click a dirty cell, select the invisible character at the end in the formula bar, and copy it (Ctrl+C).
Select the column and press Ctrl+H.
Paste the character into Find what, type a single space (or nothing, for numbers) in Replace with, then click Replace All.

Many guides suggest typing Alt+0160 on the numeric keypad in the Find box instead. Copying the real character works on every keyboard and on Mac.

Google Sheets: what’s different

TRIM: Google’s documentation states it directly: “Whitespace or non-breaking space will not be trimmed.” The same Step 2 formula works, and in Sheets CHAR(160) is also safe, because Google’s CHAR uses the Unicode table.
Data > Data cleanup > Trim whitespace: handy, but Google’s help page notes that it doesn’t trim non-breaking spaces either.
REGEXREPLACE can handle several characters at once:

=TRIM(REGEXREPLACE(REGEXREPLACE(A2,"[\x{00A0}\x{2009}\x{202F}\x{3000}\t\n\r]"," "),"[\x{200B}\x{FEFF}]",""))

Google Sheets uses RE2 syntax, where \x{00A0} means code 160 (hex A0).

Find and replace (Ctrl+H) with Search using regular expressions ticked accepts the same pattern, for example \x{00A0}.

Diagnostic checklist

What you see	Test	Fix
`LEN(A2)` > visible characters, TRIM changes nothing	X-ray formula shows 160	`TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))`
Words run together after CLEAN	Codes 10/13 between words	Replace CHAR(10) with ” ” before CLEAN
Still too long after SUBSTITUTE 160	Codes 8239, 8201, 12288	Add a SUBSTITUTE for each (Step 3)
Length is 1 more than expected, nothing visible	8203 or 65279	SUBSTITUTE with ""
Clean text, but SUM ignores it	`=ISTEXT(B2)` is TRUE	`--TRIM(…)` or VALUE
Clean column, lookups still fail	Formula references	Paste values over the original

Common mistakes

Replacing CHAR(160) with "" in text. It deletes word separators. Use ” ” for text and "" for numbers.
Running CLEAN on multi-line text without replacing line breaks first. It glues the lines together.
Relying on CHAR(160) in files opened on a Mac. Use UNICHAR(160).
Trimming a helper column and forgetting to paste values back. Your reports still read the dirty data.
Assuming Data cleanup > Trim whitespace in Google Sheets handles everything. Google says it skips non-breaking spaces.

FAQ

Why is TRIM not removing spaces in Excel?

The spaces are probably non-breaking spaces (code 160), which TRIM doesn’t touch. Check with =UNICODE(RIGHT(A2)) and fix with =TRIM(SUBSTITUTE(A2,UNICHAR(160),” ”)).

How do I remove CHAR(160) in Excel?

Use SUBSTITUTE to replace it with a normal space, then TRIM: =TRIM(SUBSTITUTE(A2,UNICHAR(160),” ”)). For numbers, replace it with nothing and convert: =—SUBSTITUTE(A2,UNICHAR(160),"").

Does TRIM remove line breaks?

No. TRIM only removes normal spaces. CLEAN removes line breaks, but replace them with a space first so words don’t merge: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10),” ”))).

Does TRIM remove non-breaking spaces in Google Sheets?

No. Google’s documentation says non-breaking spaces are not trimmed by the TRIM function or by Data cleanup > Trim whitespace. Use SUBSTITUTE or REGEXREPLACE first.

How can I see hidden characters in a cell?

List each character’s code with =TEXTJOIN(”,“,TRUE,UNICODE(MID(A2,SEQUENCE(LEN(A2)),1))). A normal space is 32, and anything like 160, 8203 or 10 is a hidden character.

Live-test findings

Step 3 and LET now both replace thin spaces (U+2009) and tabs, and remove BOM (U+FEFF) as well as U+200B. LET uses step1/step2: t1/t2 are cell addresses and are invalid names in Excel. These are targeted replacements, not a guarantee for every Unicode separator.

On the tested Mac, UNICODE(CHAR(160)) was 8224 (a dagger), not 160. Use UNICHAR(160) for cross-platform files. In Sheets, both CHAR(160) and UNICHAR(160) produced 160, but TRIM left the non-breaking space intact. The Sheets REGEXREPLACE version also passed.

Sources

Microsoft Support: TRIM function (removes only the 7-bit ASCII space, character 32): https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9
Microsoft Support: Top ten ways to clean your data (spaces 32 and 160; nonprinting characters; TRIM, CLEAN and SUBSTITUTE roles): https://support.microsoft.com/en-us/excel/top-ten-ways-to-clean-your-data
Microsoft Support: CLEAN function (removes codes 0–31): https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41
Microsoft Support: CODE function (Windows ANSI vs Macintosh character set): https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928
Google Docs Editors Help: TRIM function (“Whitespace or non-breaking space will not be trimmed”): https://support.google.com/docs/answer/3094140
Google Docs Editors Help: Split text, remove duplicates, or trim whitespace (Trim whitespace skips non-breaking spaces): https://support.google.com/docs/answer/6325535
Google Docs Editors Help: Google Sheets function list (CHAR uses the Unicode table; CLEAN removes non-printable ASCII): https://support.google.com/docs/table/25273
Google Docs Editors Help: Search and use find and replace (regular expression option): https://support.google.com/docs/answer/62754
LibreOffice Calc 25.2: local verification of the X-ray formula, the Step 3 formula, CHAR(160) vs UNICHAR(160), and TRIM output being text.