Remove invisible spaces that break Excel matches

Direct answer

For ordinary spaces and the common nonbreaking space U+00A0, use =TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")) in Excel. It replaces that specific Unicode character with an ordinary space, then removes surrounding spaces and reduces repeated ordinary spaces between words. Test the rule on both sides of a lookup before replacing your source values.

This is a targeted cleanup rule. It is not a universal instruction to delete every invisible character. A space can be part of a meaningful code or name, and two values that look similar can still represent different records.

Why TRIM alone can leave a mismatch

Microsoft states that TRIM handles the ordinary ASCII space but does not remove the nonbreaking space whose decimal code is 160. That character often arrives in copied web text. See Microsoft’s TRIM documentation.

The downloadable example contains real Unicode characters, not the literal text \u00a0. The following notation makes otherwise invisible differences visible:

Input shown with markers	Rule	Expected output
[space][space]ACME[space][space]West[space][space]	Trim ordinary spaces	ACME West
[NBSP]ACME[NBSP]West[NBSP]	Replace NBSP, then trim	ACME West
ACME[narrow NBSP]West	Leave unrecognized character	Still distinct
AB[space]CD	Preserve one internal space	AB CD

The third row intentionally remains different. A narrow nonbreaking space is U+202F, decimal 8239, and is a different character from U+00A0. Including it in the fixture stops a partial fix from looking like universal Unicode cleanup.

Inspect and clean a helper column

These Excel formulas are documentation-based guidance; we did not test the Excel interface or calculation engine. Use an installation that supports UNICHAR and UNICODE. Formula argument separators may vary with locale.

1. Keep the raw lookup key in A2. Put =LEN(A2) in a diagnostic column and compare it with a known-good key’s length.
2. Where a suspect character is at position 5, inspect it with =UNICODE(MID(A2,5,1)). Change the position to the actual character being investigated. Do not use this on an empty extraction.
3. In B2, enter the targeted formula below and fill down the intended key range.
4. Apply the same documented rule to the other lookup table, preserving both originals.
5. Compare cleaned keys and inspect collisions before using them as unique identifiers.

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

If inspection confirms that U+202F should also be treated as a space in this particular field, extend the rule deliberately:

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

The extended rule changes the third fixture row. That is an intentional policy change, so update your expected result when you choose it rather than silently treating the sample’s original expectation as wrong.

Check matching and collisions

Under the first formula’s rule, the first two sample rows become ACME West, with nine characters. AB CD retains its internal space and five-character length. The narrow-space row retains nine characters too, showing why equal length does not prove equal text.

Two rows collapsing to the same cleaned key may be exactly what you wanted when matching spellings. It may also mean the cleaned field is unsuitable as a unique record ID. Count repeated cleaned keys and inspect their original rows before removing duplicates. A successful lookup is not proof that it selected the intended record if multiple candidates now share the key.

What this does not fix

This formula does not remove zero-width characters, tabs, line breaks, or all Unicode whitespace. It also does not normalize accents, punctuation, or letter case. Investigate those differences separately instead of making the cleanup rule increasingly aggressive without evidence.

For identifiers where internal spaces are meaningful, TRIM’s collapse of repeated ordinary spaces may be too destructive. Use a field-specific policy agreed with the exporter. If the cleaned value is a quantity, continue with numeric-text conversion. If you are aligning CSV headers, see combining reordered CSV columns.

Testing record

Python checks validated the actual Unicode code points and the sample outputs under the explicitly stated ordinary-space/U+00A0 rule. This is a reference transformation, not an Excel execution test. The narrow-space case is deliberately retained to document the rule’s boundary.

Verified Google Sheets formula check

We ran the targeted cleanup in Google Sheets on 8 September 2026, using Chrome 152.0.7977.82 on macOS 26.3. The workbook used the Poland locale, Polish menus, and English function names.

1. Prepare the four source strings in a scratch sheet. To preserve surrounding ordinary spaces during this test, enter =" ACME West ". Pasting text had trimmed those surrounding spaces, so we checked the raw length before proceeding.
2. Enter =TRIM(SUBSTITUTE(A2;UNICHAR(160);" ")) in a helper cell for the corresponding source row. This locale requires semicolons between arguments.
3. Use LEN on both columns. Use UNICODE with LEFT or MID to confirm the suspect source code point. Compare the first three cleaned strings with EXACT rather than relying on their appearance.

The ordinary-space input had 14 characters and the U+00A0 input had 11. Both became the same nine-character ACME West. The U+202F case stayed distinct, even though its cleaned length was also nine. AB CD retained five characters and its internal space.

This is a real Sheets calculation test. The extended U+202F replacement and Excel execution were not tested.

Source references

• Microsoft TRIM function

Verification scope: Python 3 standard-library fixture checks; Google Sheets web, 2026-09-08; Chrome 152.0.7977.82; macOS 26.3; Poland locale; Polish menus; English function names; semicolon arguments; Excel UI and calculation engine not tested. See the testing notes above for limitations.