5.3 Extra Spaces and Non-printable Characters
Before
| Store ID (raw) | LEN |
|---|---|
| " BLK-PUN-01" | 12 |
| "BLK-NSK-01 " | 13 |
| "AMN-NGP-01" + non-breaking space | 11 |
| "BLK-KOP-01" + line break | 11 |
After
| Store ID (clean) | LEN |
|---|---|
| BLK-PUN-01 | 10 |
| BLK-NSK-01 | 10 |
| AMN-NGP-01 | 10 |
| BLK-KOP-01 | 10 |
Steps in Excel
- Check:
=LEN(A2)– more characters than you can see means hidden spaces. - Normal spaces:
=TRIM(A2). - Line breaks and control characters:
=CLEAN(A2). - Non-breaking spaces from web/PDF copies:
=SUBSTITUTE(A2,CHAR(160)," "). - All together:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))› copy down › Paste Special › Values over the original. - Find & Replace alternative for CHAR(160): Ctrl + H › in Find what hold Alt and type 0160 on the numeric keypad › Replace with a normal space › Replace All.
Ravindra Bagale's Tip
Many students apply TRIM and think "it's clean now", but don't check LEN again. Data from the web or a PDF contains CHAR(160), which TRIM doesn't see. So after cleaning, check a sample with LEN – if you expect 10 characters, you should get exactly 10.
Ravindra Bagale's Tip – मराठी
बरेच students TRIM लावून "झालं clean" समजतात, पण LEN परत check करत नाहीत. Web किंवा PDF मधून आलेल्या data मध्ये CHAR(160) असतो, जो TRIM ला दिसत नाही. म्हणून cleaning नंतर LEN ने एक sample check करा – 10 अक्षरं अपेक्षित असतील तर 10 च आली पाहिजेत.
Ravindra Bagale's Tip – हिंदी
बहुत से students TRIM लगाकर "हो गया clean" मान लेते हैं, पर LEN दोबारा check नहीं करते. Web या PDF से आए data में CHAR(160) होता है, जो TRIM को दिखता नहीं. इसलिए cleaning के बाद LEN से एक sample check करो – 10 अक्षर चाहिए तो ठीक 10 ही आने चाहिए.
Practice task
Paste a few store IDs copied from a web page, measure LEN, clean them with the combined formula and prove they now match the Stores master using XLOOKUP.