Clean Numbers in a TSV

currency · thousands separators · decimal comma · (negatives) · percent · runs in your browser

Numbers that came out of a finance system, a European locale, or a spreadsheet's display formatting are not numbers any more — they're text that looks numeric. $1,234.50, 1.234,50 €, (500) and 12% all fail to load as a numeric column and all silently become text or zero somewhere downstream. This turns them back into plain machine-readable numbers, one column or the whole file.

How to use

1. Paste or drop the file. The first row is treated as the header and is never touched.
2. Name the numeric columns if you know them. Leave it blank and every cell containing a digit is attempted, with anything that doesn't parse left alone.
3. Set the decimal mark if the file has a consistent locale. Detect per cell is right for mixed data but can misread a value like 1,234 — see below.
4. Check the status line. It reports how many cells were normalised and how many could not be parsed.

The 1,234 ambiguity

1,234 is one thousand two hundred and thirty-four in the US and one point two three four in Germany. Nothing can resolve that from the value alone. Per-cell detection uses the position rule — whichever of . and , appears last is the decimal mark — which handles 1,234.50 and 1.234,50 correctly, and resolves the bare 1,234 as a thousands separator. If your file is European, set the decimal mark to comma explicitly rather than trusting detection, and the same value becomes 1.234.

Spaces, non-breaking spaces, apostrophes and underscores are always treated as thousands separators, which covers the French, Swiss and programmer conventions.

What each option actually does

Strip symbols removes any character that isn't a digit, sign, separator or exponent marker — so currency symbols and three-letter codes go, wherever they sit. Turn it off if a column mixes numbers with meaningful text you'd rather see fail.

(123) is −123 is the accounting convention for negatives. It's on by default because a parenthesised number that keeps its parentheses is guaranteed to be wrong downstream, and one that loses them silently becomes a positive — which is worse than wrong, it's inverted.

Percent has three honest choices. Drop the sign keeps 12% → 12 and is right when the column is understood to be a percentage. Divide by 100 gives 0.12 and is right when the consumer expects a ratio. Keep normalises the number but leaves the sign, for display. Pick deliberately: this is the option most likely to produce a hundred-fold error if you get it wrong.

If unparseable defaults to leaving the original text in place, so nothing is destroyed by a bad guess. Blanking is the choice when you're about to load into a typed column and would rather have a NULL than a load failure. Either way the count is in the status line — if it's high, the column probably isn't numeric at all.

FAQ

Privacy

100% client-side. No upload. See the privacy policy.