Two copied labels can look identical while containing different characters. One common cause is a nonbreaking space from web content. Excel’s TRIM removes ordinary ASCII spaces at the edges and reduces repeated ordinary spaces between words, but it does not remove the Unicode nonbreaking space with value 160 by itself.

Before changing the data, decide whether spacing is meaningful in the identifier. A catalogue code with deliberately repeated spaces needs a different rule from a human-readable location label. Keep the original column and develop any cleaned version alongside it.

Test a narrowly defined replacement

For a label in A2, this helper formula first replaces nonbreaking spaces with ordinary spaces, then applies TRIM:

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

UNICHAR supplies the character for the Unicode number. SUBSTITUTE replaces the specified text; with no instance argument, it replaces every occurrence. The final TRIM step also collapses repeated ordinary spaces inside the text, so include that effect in the agreed cleanup rule.

Create a disposable test in A2 using =UNICHAR(160)&"Rack A"&UNICHAR(160). There are two ordinary spaces between Rack and A. The helper should produce Rack A with one internal space and no leading or trailing spaces.

If preserving those two internal spaces is required, do not accept this combined formula merely because it makes a lookup succeed. The test has shown that it changes information your task may need.

Compare the cleaned keys before adopting them

Inspect several real examples alongside their originals, including one already clean label and one with meaningful punctuation. Check whether two originally different records now produce the same cleaned key. A normalisation rule can create an ambiguity even when each individual result looks tidy.

Use the accepted helper values for a small matching trial and inspect both successful and still-unmatched records. This formula targets one particular character and ordinary spacing; other differences require their own diagnosis.

Retain the source text and document the exact rule with the import. That gives the next person a way to reproduce the transformation without treating all visually empty characters or every remaining mismatch as interchangeable.

Sources: Microsoft TRIM; Microsoft SUBSTITUTE; Microsoft UNICHAR.