Clean Numbers in a CSV

Number cleaning turns figures written for a person into figures a database will accept. $1,234.50, 1.234,50 EUR, (89.00), 12% and 1 250,75 all become plain numbers. The decimal mark is decided per column from its own punctuation. It runs in this tab, with nothing uploaded.

Numbers fine but the dates a mess? The date normalizer is next door.

1,234 is two different numbers

In Chicago it is one thousand two hundred and thirty four. In Berlin it is one and a bit. Nothing inside that cell can tell you which, and a tool that strips commas unconditionally turns every German price into a number a thousand times too big without a word of complaint.

The answer, like the day-first question on the date page, is not in the value. It is in the column. Three kinds of evidence, in order of strength:

Both marks in one value. 1.204,50 can only be decimal comma; 1,204.50 can only be decimal point. Whichever comes last is the decimal.
A comma not followed by exactly three digits. 88,25 has two digits after it, so that comma is a decimal mark. Nobody groups thousands in twos.
A point not followed by exactly three digits. Same argument the other way round.

A value like 1,204 on its own is genuinely ambiguous and counts as evidence for neither. The column is decided by whatever the rest of its values agree on, and the per-column line in the report says which reading it used. When the whole column is ambiguous the reading defaults to a decimal point, and pinning Decimal mark is a single click.

Worked example: six ways to write money

A single column, six rows, all of them a real thing an export has produced:

total
"$1,204.50"
$88.00
(89.00)
USD 40
12%
n/a

Tick the column, switch Brackets to Mean negative, and leave everything else alone:

total
1204.5
88
-89
40
12
n/a

6 rows · cleaned total · 5 values changed · 1 percent sign removed
· 1 value read as negative · 1 value left as found
total: 5 cleaned, 1 left alone (read as decimal point)

Five conversions and one honest refusal. n/a is not a number, so it is left exactly as it was and counted, with the warning naming it. That is the right outcome: blanking it would destroy the information that somebody deliberately recorded a non-answer, and turning it into 0 would be a lie.

Now the same column written for a European audience:

total              becomes
"1.204,50"         1204.5
"88,25"            88.25
"1 250,75"         1250.75

No setting changed between those two runs. The first column was read as decimal point because 1,204.50 says so; the second as decimal comma because 1.204,50 and 88,25 both do. The space in 1 250,75 is a French thousands separator, and it is removed along with the non-breaking and narrow variants of it that formatted output actually uses.

Brackets are off by default, and here is why

In accounting, (89.00) means minus 89. It is a real convention, it comes out of most finance systems, and reading it correctly is genuinely useful.

It is also a bracket. A column that mixes figures with the occasional note in it, which is most columns in most real exports, will have things like 1200 (estimate) in it, and a tool that treats every bracket as a sign flip will silently invert them. Flipping the sign of a number is about the worst thing a cleaning tool can do quietly, because the value still looks plausible and the total is now wrong by twice the amount.

So the switch is off until you turn it on, and when you do the report counts how many values it flipped. If that count is higher than you expected, look at the rows. The same logic applies to a trailing minus, 89.00-, which some mainframe exports produce; that one is handled without a switch, because a trailing minus on an otherwise numeric value is not ambiguous.

Percentages, and the question nobody asks first

12% can reasonably become 12 or 0.12, and which one is right depends entirely on what the column is for. A discount percentage stored as 12 and used as price * (1 - d/100) wants the first. A rate about to be multiplied straight into a figure wants the second.

The default is to drop the sign and keep the digits, because it is the smaller change and because a file where 12% has become 0.12 without anyone noticing is a file whose report is out by two orders of magnitude. Switching to Divide by 100 is one click, and either way the report counts how many percent signs it saw so the decision is visible.

When you do divide by 100, set Round to as well. Dividing 7.5 by 100 in binary floating point produces 0.075 exactly, but plenty of other values do not, and rounding to four or five decimals keeps the column tidy. The page already trims results to fifteen significant digits to clear the obvious artefacts, and an explicit decimal count is better than relying on that.

What gets stripped

Currency symbols. The symbol set covers dollar, pound, euro, yen, rupee, ruble, won, shekel, naira, peso, baht, dong, hryvnia and lira, plus the Brazilian R$. Three-letter codes are recognized as words, so USD 40 and 40 EUR both work, and a column of product codes that happens to contain the letters USD inside a longer word is left alone.
Thousands separators. Commas or points depending on the column's decimal mark, plus ordinary spaces, non-breaking spaces, narrow non-breaking spaces and apostrophes, which is how Swiss formatting does it.
Signs. A leading plus is dropped. A leading or trailing minus is kept as a sign.
Nothing else. A value that still has letters in it after the currency handling is not a number, and it is left as found rather than being scraped for digits. approx 40 stays approx 40.

There is one more guard worth knowing about. If a value still contains two decimal points after the separators have been handled, it is rejected rather than parsed, because that means the reading was wrong. 1.204.50 under a decimal-point reading is not a number anyone meant, and producing 1.204 from it would be worse than saying so.

Detection, and when to tick the box yourself

Leave the checkboxes empty and each column is sampled: if at least half of its non-blank values contain a digit and some piece of formatting, the column is treated as numbers. The formatting requirement is the important half. A column of plain integers does not need this tool and is left alone, so running the page over a whole file will not quietly reformat your identifiers.

That also means detection will skip a column that is only occasionally formatted, which is exactly the column you most want cleaned. Tick it and it is processed whatever the sample said, with everything unreadable counted and quoted back at you.

Afterwards, two numbers in the report are worth a look. The count of values left as found tells you how much of the column was never numeric, which is often a surprise. And the per-column line names the decimal mark that was used, which is the one thing you should sanity-check before importing the file into anything that will do arithmetic on it.

Frequently Asked Questions

How does it know whether a comma is a decimal point or a thousands separator?

By reading the punctuation across the whole column rather than one value. A value with both marks settles it, since whichever comes last is the decimal. A comma followed by anything other than exactly three digits must be a decimal mark, because nobody groups thousands in twos. The per-column report names the reading it used.

What happens to a value it cannot read?

It is left exactly as it was and counted, with the first few quoted back in the warning. Blanking it would destroy the fact that somebody deliberately recorded a non-answer, and turning it into 0 would be a lie. Values like n/a and approx 40 come through untouched.

Why is 12% turned into 12 rather than 0.12?

Because dropping the sign is the smaller change, and a file where 12% has quietly become 0.12 is a report out by two orders of magnitude. Switch Percentages to Divide by 100 when the fraction is what you want, and set Round to at the same time so the decimals stay tidy.

Does it read (89.00) as minus 89?

Only if you switch Brackets to Mean negative, which is off by default. The accounting convention is real, and so is a column that contains 1200 (estimate), and flipping the sign of a value quietly is about the worst thing a cleaning tool can do. When you turn it on, the report counts how many values it flipped.

Which currency symbols does it handle?

Dollar, pound, euro, yen, rupee, ruble, won, shekel, naira, peso, baht, dong, hryvnia, lira and the Brazilian R$, plus around two dozen three-letter codes recognized as whole words, so USD 40 and 40 EUR both work while a product code containing those letters inside a longer word is left alone.

Will it reformat my ID columns by accident?

Not with automatic detection. A column only qualifies when at least half its values contain a digit and some piece of formatting, so a column of plain integers or zero-padded identifiers is skipped. If you tick a column yourself, it is processed whatever it holds, so tick deliberately.

What about spaces used as thousands separators?

Handled. Ordinary spaces, non-breaking spaces, narrow non-breaking spaces and apostrophes are all removed, which covers French, Scandinavian and Swiss formatting. 1 250,75 becomes 1250.75 with no extra settings.

Is my file uploaded?

No. There is no upload endpoint on this page. The file is read, parsed and rewritten by JavaScript in your own tab, and nothing is kept between visits.

Money in, numbers out

Free, no account, no upload. Drop the file, check the decimal mark it chose, take the CSV.

Back to the number cleaner