Find and Remove Hidden Spaces in Excel Identifiers
Two IDs that look like AB12 may differ byte by byte. Identify the character first; then use the narrowest cleanup that matches your data policy.
Inspect five apparently similar IDs
The workbook's A4:A8 includes clean AB12, outer ordinary spaces, a trailing nonbreaking space, an embedded tab and an embedded line feed. B4:B8 shows lengths. C4:C8 records fixed code references for this sample: row 6 is 160 (NBSP), row 7 is 9 (tab); G4:G8 gives the character positions. For changed inputs, calculate =UNICODE(MID(A4,G4,1)) in a new Excel cell. The code references do not update with edits; H4 contains the literal NBSP used by the cleanup formula. Formula =TRIM(CLEAN(SUBSTITUTE(A4,$H$4," "))) is copied to D4:D8. For this specific sample, all cleaned values should equal AB12, and E4:E8 should be TRUE.
| Observed character | Targeted action | Why plain TRIM misses it |
|---|---|---|
| ordinary outer spaces, code 32 | TRIM | TRIM is designed for these |
| NBSP, code 160 | SUBSTITUTE(text,$H$4," ") then TRIM | NBSP is a different character |
| tab 9 or line feed 10 | CLEAN when removal is appropriate | TRIM does not remove control characters |
Check before changing source data
- Copy the raw ID column so the original stays available. Use
LEN,UNICODE(MID(...))andEXACTto inspect a failing example. - Decide whether spaces inside IDs are meaningful. The workbook's combined cleanup deliberately removes tab and line feed; it would merge
AB+ tab +12into AB12. Use that only when the source contract permits it. - Fill the formula down, compare clean IDs to the known key, then paste values only after a review. Check whether formerly different records became duplicates.
Microsoft notes that TRIM/CLEAN target a subset of characters and names NBSP 160 separately. Other Unicode whitespace is outside this sample; do not call the formula a universal sanitizer. The result can affect lookup and duplicate rules.