Why doesn't TRIM remove all the spaces?

Excel's TRIM only removes the normal space (character 32). Non-breaking spaces (character 160) survive, so replace them first with SUBSTITUTE(A2, CHAR(160), " ").

How do I see hidden characters in a cell?

Compare LEN(A2) with what you can count, and use CODE(RIGHT(A2)) or CODE(LEFT(A2)) to read the code of the last or first character.

Does TRIM change the value inside the text?

It reduces repeated internal spaces to a single space as well as removing leading and trailing ones. That's usually what you want for keys, but check names where double spaces were intentional.

Will cleaning slow down a large workbook?

Helper columns with TRIM and SUBSTITUTE are cheap. For very large files, clean once in Power Query or paste the cleaned values over the originals.

Can a space in the middle of a key cause the same problem?

Yes. 'ABC 100' and 'ABC-100' or 'ABC 100' with two spaces are different values. TRIM fixes doubled spaces, but a space versus a dash needs SUBSTITUTE or a deliberate normalisation rule.

How trailing and non-breaking spaces break matches between spreadsheets

By the Analistable team · Updated 2026-10-10 · 5 min read

If "ABC-100" and "ABC-100 " look identical but won't match, one has an invisible character. Compare =LEN(A2) with the number of characters you can see; if it's higher, clean the key with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")), which turns non-breaking spaces into normal spaces and then strips leading, trailing and doubled spaces. Clean both sides before matching.

Part of our guide: How to join spreadsheets on a common column

Why invisible characters break matches

Lookups and joins compare text character by character. A trailing space is a real character, so ABC-100 and ABC-100 are different values even though the cells look the same. Exports are the usual source: fixed-width systems pad fields with spaces, copy-pasting from web pages and PDFs brings in non-breaking spaces (character 160 rather than 32), and some tools add line breaks or tab characters inside cells.

The symptoms are recognisable:

XLOOKUP or VLOOKUP returns #N/A for a value you can see in the other list.
COUNTIF says 0 for a value that is obviously present.
A join or Power Query merge leaves rows unmatched, but only some of them — typically all rows from one export.
Remove Duplicates keeps two copies of what looks like the same value.

Detect the problem with LEN and CODE

Put the suspect values side by side and check their length. A difference you can't see is an invisible character.

Diagnosing two SKUs that look identical
Cell	Shows	=LEN()	=CODE(RIGHT(A2))	Meaning
Sheet1!A2	ABC-100	7	48	Clean: last character is 0
Sheet2!A2	ABC-100	8	32	Trailing normal space
Sheet2!A3	ABC-100	8	160	Trailing non-breaking space
Sheet2!A4	ABC-100	8	10	Line break at the end

=CODE(RIGHT(A2)) shows the code of the last character; =CODE(LEFT(A2)) checks the first. For a quick test of a whole column, =A2=TRIM(A2) returns FALSE for any cell with extra spaces. To find non-breaking spaces anywhere in the text, use =ISNUMBER(SEARCH(CHAR(160), A2)).

In Microsoft 365, CODE returns a code for the character set in use; for characters beyond the basic set, UNICODE() is the safer check. A non-breaking space is 160 with either function.

Clean the keys with TRIM, SUBSTITUTE and CLEAN

TRIM removes leading and trailing spaces and reduces runs of spaces inside the text to one. In Excel it only handles the normal space (character 32).
SUBSTITUTE(A2, CHAR(160), " ") converts non-breaking spaces to normal spaces so TRIM can deal with them.
CLEAN removes non-printing control characters 0–31, which includes line breaks (10) and tabs (9).

Combined, in a helper column on each sheet:

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

Then look up the cleaned column rather than the original. If you can't add helper columns, clean inside the formula in Microsoft 365 — XLOOKUP accepts a calculated lookup array:

=XLOOKUP(TRIM(F2), TRIM(SUBSTITUTE(Sheet2!A2:A500, CHAR(160), " ")), Sheet2!C2:C500, "not found")

Find & Replace also works for a one-off fix: in the Find box hold Alt and type 0160 on the numeric keypad (Windows) to enter a non-breaking space, leave Replace empty, and replace all. Paste the cleaned column as values when you're done.

In Google Sheets the same formulas work. Sheets' TRIM behaviour with non-breaking spaces has differed from Excel's at times, so keep the SUBSTITUTE step rather than relying on TRIM alone.

Clean in Power Query

Select the key column, then Transform → Format → Trim and Format → Clean. Trim in Power Query removes leading and trailing whitespace; to be sure about non-breaking spaces, add a Replace Values step first, replacing #(00A0) with a normal space (tick Replace using special characters, or edit the M):

= Table.TransformColumns(Source, {{"SKU", each Text.Trim(Text.Clean(Text.Replace(_, "#(00A0)", " "))), type text}})

Apply the same step to both queries before Merge Queries. Because the step is saved, next month's export is cleaned automatically.

Clean in SQL

SELECT a.sku, a.qty, b.price
FROM stock a
JOIN prices b
  ON trim(replace(a.sku, chr(160), ' ')) = trim(replace(b.sku, chr(160), ' '));

This is DuckDB syntax; chr(160) returns the non-breaking space. Analistable loads sheets into DuckDB in your browser, and when you ask a question across two files you can ask it to trim the key columns before joining — the SQL it writes is shown, so you can check the cleaning step is there.

Worked example: before and after cleaning

Stock sheet matched to a price list exported from a web shop
SKU (stock)	SKU (price list)	Match before	Match after cleaning
ABC-100	ABC-100␠	No	Yes
ABC-101	ABC-101	Yes	Yes
ABC-102	ABC-102⍽ (char 160)	No	Yes
ABC-103	␠ABC-103	No	Yes
ABC-104	—	No	No (genuinely missing)

Matches go from 1 of 5 to 4 of 5. The remaining miss is real, which is the point: cleaning removes false mismatches so the true ones stand out. For a list-to-list check, see finding matches between two lists; for picking the lookup function, see VLOOKUP vs XLOOKUP vs INDEX MATCH.

Prevent it next time

Clean keys in a fixed step (a Power Query step, or a helper column) rather than by hand each month.
Keep the original column; look up against the cleaned copy so you can audit what changed.
Add a check column =LEN(A2)<>LEN(TRIM(A2)) and filter it after each import.
If you control the export, ask for keys without padding — it fixes every downstream sheet at once.

Spaces are one of several causes of failed matches; the join spreadsheets guide covers the rest, including numbers stored as text.

Frequently asked questions

Why doesn't TRIM remove all the spaces?: Excel's TRIM only removes the normal space (character 32). Non-breaking spaces (character 160) survive, so replace them first with SUBSTITUTE(A2, CHAR(160), " ").
How do I see hidden characters in a cell?: Compare LEN(A2) with what you can count, and use CODE(RIGHT(A2)) or CODE(LEFT(A2)) to read the code of the last or first character.
Does TRIM change the value inside the text?: It reduces repeated internal spaces to a single space as well as removing leading and trailing ones. That's usually what you want for keys, but check names where double spaces were intentional.
Will cleaning slow down a large workbook?: Helper columns with TRIM and SUBSTITUTE are cheap. For very large files, clean once in Power Query or paste the cleaned values over the originals.
Can a space in the middle of a key cause the same problem?: Yes. 'ABC 100' and 'ABC-100' or 'ABC 100' with two spaces are different values. TRIM fixes doubled spaces, but a space versus a dash needs SUBSTITUTE or a deliberate normalisation rule.