Text & String Formulas
Normalize Dirty Text Keys: Strip CHAR(160), Control Codes & Case (CLEAN & TRIM)
Sanitize web-scraped and ERP-exported lookup keys via LOWER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))) so invisible HTML non-breaking spaces and line breaks stop breaking XLOOKUP and VLOOKUP.
Why TRIM(CLEAN(A2)) STILL misses HTML non-breaking spaces CHAR(160)!
Few spreadsheet mysteries waste more analyst hours than two SKU codes that look identical on screen ("SKU 104" vs "SKU 104") yet return #N/A in XLOOKUP! Why does =TRIM(CLEAN(A2)) fail to fix web-copied or CRM-exported strings? Because CLEAN() strips only ASCII control codes 0–31 (like tabs CHAR(9) and line breaks CHAR(10)), while TRIM() collapses only standard ASCII 32 spaces—leaving Unicode/Latin-1 non-breaking space CHAR(160) ( ) completely untouched!
Nesting SUBSTITUTE(A2, CHAR(160), " ") INSIDE CLEAN and TRIM, then standardizing letter case with LOWER(...), builds an industrial-strength key normalizer: =LOWER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))))! Watch a messy ERP export string with leading spaces, an embedded CHAR(160), and a trailing newline: raw LEN is 11 characters, whereas normalized output collapses cleanly to 7-character "sku 104"!
| Dirty Raw Input Character Defect | TRIM(CLEAN(A2)) Alone | Full SUBSTITUTE(CHAR(160)) Pipeline | Lookup Match Outcome |
|---|---|---|---|
| Leading/Trailing ASCII 32 Spaces | Removed | Removed | Matches Clean Key |
| Embedded Line Break CHAR(10) / Tab CHAR(9) | Removed by CLEAN | Removed by CLEAN | Matches Clean Key |
| HTML Non-Breaking Space CHAR(160) | MISSED (Stays 160!) | Converted to " " & Trimmed! | Fixes Silent #N/A Bug! |
Full Canonical Text Key Normalizer (CHAR(160) + Control Codes + Case)
Replaces CHAR(160) with standard space first so outer CLEAN and TRIM collapse all whitespace runs into single spaces before LOWER standardizes case.
=LOWER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))))Hidden Junk Character Count Stripped During Normalization
Measures LEN(A2) - LEN(normalized) to audit how many invisible whitespace or control bytes polluted the raw cell.
=LEN(A2) - LEN(LOWER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))))Try the example
Use the original values or change them to test your understanding.