Remove Invisible Zero-Width Spaces and Control Characters from Huge CSVs
Data copied from websites or Salesforce often contains 'ghost' characters: the Zero-Width Space (U+200B), Zero-Width Joiner (U+200D), or Non-Breaking Space (U+00A0). These are literally invisible in Excel, but they change the string's byte-length. If you search for 'A101' in your CRM, and the record is stored as 'A101[U+200B]', the search returns 0 results. This leads to massive duplicate creation. This workflow perform a deep regex scan of every cell, identifies 15+ types of invisible Unicode pollutants, and purges them while preserving legitimate text and formatting.
Why This Matters
A logistics company couldn't find 1,200 shipping IDs in their database because the data-entry team had copied them from a web portal that injected U+200B characters. They manually re-entered the data, creating 1,200 duplicates and breaking their inventory logic. Total wasted labor: 35 hours. These invisible characters are 'Data Poisoning'—they break VLOOKUP, SQL JOIN, and CRM search silently.
Why Excel & Python Fail Here
You cannot see these characters. In Excel, 'Text' and 'Text ' look identical, but '=LEN()' will return different numbers. Standard Find-and-Replace in Excel cannot target U+200B. Only a regex-based Unicode sweep can safely identify and remove these bytes without corrupting the surrounding text.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the Format Cleaner to execute this entirely in your browser.
- 1
Upload your 'Ghosted' CSV
Drag the file into the tool. It scans all columns for non-printable Unicode ranges. A report shows: 'Found 4,281 invisible characters in 3 columns'.
- 2
Cleanse invisible pollution
The tool strips U+200B (ZWSP), U+FEFF (BOM inside strings), U+0000-U+001F (Control Chars), and normalizes U+00A0 (NBSP) to standard spaces.
- 3
Export 'Clean Byte' CSV
Download the file. Your IDs and names are now truly identical to their text representation. CRM searches and VLOOKUPs will work 100% accurately.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
LEN(A2) returns 5 but I only see 4 chars | Hidden Zero-Width Space (U+200B) at the end or middle of the string. | Use the Format Cleaner tool to strip non-printable Unicode ranges. |
Frequently Asked Questions
What is a Zero-Width Space?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly