SPREADSHEET TEXT · A PRACTICAL CHECK

When Excel cells look equal but won’t match.

Text pasted from websites, PDFs, and exports can carry characters that an ordinary space cleanup misses. Inspect one problematic cell before changing an entire column.

Start with a small, representative sample

Copy the cell’s text into the inspector. Look for NO-BREAK SPACE, NARROW NO-BREAK SPACE, ZERO WIDTH SPACE, or a BOM inside the value. Two product names can look identical while one contains U+00A0 and the other contains an ordinary U+0020 space.

Detection does not prove the hidden character is the only cause. Number-versus-text differences, case, punctuation, and formula behavior can also explain a mismatch.

Why TRIM and CLEAN may leave characters behind

Excel’s TRIM function targets ordinary ASCII spaces. A non-breaking space with decimal value 160 needs a separate substitution. CLEAN targets a set of nonprinting ASCII characters and does not remove every invisible Unicode character.

For a cell in A1, these formulas illustrate two targeted changes. Function names and separators can differ in localized Excel installations.

=SUBSTITUTE(A1,UNICHAR(160)," ")Replace U+00A0 with an ordinary space.=SUBSTITUTE(A1,UNICHAR(8203),"")Remove U+200B when its word-break function is unwanted.

Keep cleanup separate from the original values

Use a helper column first. Compare a few outputs and review any characters that matter for language or formatting. If you add TRIM around a substitution, remember that TRIM also changes repeated ordinary spaces. That extra change may be inappropriate for identifiers or intentionally aligned text.

The browser inspector works on pasted text, rather than reading or modifying a workbook. Its Advanced Non-breaking spaces category replaces U+00A0 and U+202F with ordinary spaces. Zero-width separators is a separate category. Copy the reviewed result back only after checking that it suits the column’s meaning.

Microsoft documents these limits and recommends targeted substitution. TRIM reference ↗ Excel data cleanup guidance ↗

See what your text contains.

Inspect the characters before deciding what to change.

Open the inspector →