Spreadsheet whitespace guide
Why the table is read before it is cleaned
In a CSV file a space is part of the value. RFC 4180 says spaces are considered part of a field and should not be ignored, and quoted fields can hold commas, double quotes and line breaks. A plain space remover that works on the raw file would also change the gaps beside delimiters, strip line endings, or glue a multi-line address into one row.
This tool does what a spreadsheet does first: it splits the text into records and fields, honoring quotes. Only then does it clean the cells you selected, and it writes the table back with quotes wherever a value needs them. Text processing happens in your browser.
The five cleaning rules
| Rule | Default | Exactly what it touches |
|---|---|---|
| 1. Convert non-breaking spaces | On | U+00A0, U+202F and U+2007 each become one U+0020. |
| 2. Remove zero-width spaces | Off | U+200B only. |
| 3. Remove U+FEFF inside cells | Off | U+FEFF in cell content, counted apart from a BOM at the start of the input. |
| 4. Trim cell edges | On | Regular spaces (U+0020) at the start and end of a cell, not at the ends of its lines. |
| 5. Collapse repeated spaces | Off | Two or more consecutive U+0020 inside a cell become one. |
Rules run in that order on every selected cell, each time from the original parsed text, so running the tool twice never compounds. Everything not listed is left alone by default: runs of spaces inside a cell, Tabs, line breaks inside quoted cells, full-width spaces such as U+3000, and joiners such as the U+200D that holds an emoji sequence together. The tool never uses a generic whitespace pattern, so it cannot reach beyond these characters.
A field-level example
Stored cell: " North Harbor[U+00A0] "
Default rules: "North Harbor"
With rule 5 on: "North Harbor"By default the non-breaking space becomes a regular space, the three spaces at the end are trimmed, and the double space inside the name is kept because it might be meaningful. Turn on rule 5 when you also want internal runs squeezed to one space.
Quoting survives the round trip too:
Input CSV:
id,note
00123," North, ""HQ"" "
Cleaned note cell: North, "HQ"
Downloaded CSV:
id,note
00123,"North, ""HQ"""The comma and the quotes are data, so they stay, and the identifier 00123 keeps its leading zeros because nothing is converted to a number.
How to clean Excel or CSV cells
- Pick the input format. For cells copied from Excel use Excel / TSV; for a saved file pick the comma or semicolon variant.
- Paste the cells, or open a UTF-8 CSV or TSV file. The preview shows how many records and columns were found. A file's extension can suggest a format, but the selected format is always shown and you can change it.
- Tick First row is a header if the first record holds column names, then untick any column that must stay exactly as it is.
- Choose the rules and select Clean selected columns.
- Check the cells marked was and now; Show changed cells only narrows the preview to them.
- Copy the result back into Excel, or download it as CSV.
When TRIM does not remove the spaces in Excel
Excel's TRIM function was written for the ordinary space character (code 32). Web pages, word processors and some exports use the non-breaking space (code 160) instead, and TRIM leaves it in place, so a cell can look trimmed and still fail a lookup. Two formulas cover the common case:
=SUBSTITUTE(A1,CHAR(160)," ")
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))The first replaces only U+00A0; it does not trim anything. The second also trims both ends and squeezes internal runs of spaces to one, so it is not the same as this tool's defaults, which keep internal runs. Neither formula handles the narrow no-break space U+202F, the figure space U+2007, the zero-width space U+200B or U+FEFF. Those are what the other rules here are for. If you only need to find them, the non-breaking space remover and the zero-width character remover explain each character.
What this version does not do
- It reads pasted text and UTF-8 CSV or TSV files. It does not open XLS or XLSX workbooks, evaluate formulas, or connect to online spreadsheets.
- Every field stays a string: no dates, numbers or booleans are detected or reformatted.
- Malformed tables are reported with a line and column and are not repaired. A quote inside an unquoted cell, a space before an opening quote, an unclosed quote, a blank line in a multi-column table or a record with a different number of fields stops the export.
- Input is limited to 5 MiB of UTF-8 text and 100,000 fields; larger input is refused rather than cut short. The preview shows 50 data records, and the rest are still processed.
- The download is always comma-separated UTF-8 with CRLF between records, even when the input used semicolons. The original quoting style and byte layout are not preserved, but reading the downloaded file back gives the same cleaned cells.
- Copy for Excel is turned off when a cleaned cell contains a Tab or a line break. Use the CSV download for those tables.
Frequently asked questions
Why is TRIM not working on my Excel cells?
The cell probably holds a non-breaking space or another character that TRIM does not treat as a space. Paste the column here with rule 1 on and the changed cells will show a NBSP tag where the old value hid one.
How do I remove spaces in Excel cells without writing formulas?
Copy the cells in Excel, paste them here as Excel / TSV, clean the columns you selected, then use Copy for Excel and paste over the original range. Check the preview first; the tool shows exactly which cells it changed.
Does it trim the spaces after a comma?
A space after a delimiter belongs to the next field, so rule 4 removes it from that cell like any other edge space. If the space sits before an opening quote, the quote is not valid CSV and the tool reports the error instead of guessing.
Will leading zeros survive?
Inside the tool, yes. The risk comes afterward, because Excel may convert 00123 to a number when it opens a CSV or receives pasted cells. Set the destination column to Text first.
Is my data sent anywhere?
The table, file contents and results are processed by scripts running in your browser and are not sent to a server by this page. Like the rest of the site, the page loads analytics and advertising scripts; see the privacy policy. Table text shown on this page is masked from session recordings.
Related cleanup tools and guides
For one stubborn character at a time, use the non-breaking space remover or the zero-width character remover. The whitespace in data cleaning guide explains how stray spaces break joins, lookups and imports, and the tool directory lists every cleanup tool on the site.