Switzerland groups thousands with an apostrophe (1'234.50); Germany, Italy and much of Europe group with a dot and write the decimals after a comma (1.234,50). Read with the wrong convention, 1.234,50 becomes 1.23 or text, and a sum is silently wrong.
Column type: NUMBER
| In the file | Cleaned | Status |
|---|---|---|
1'234.50 | 1234.50 | clean |
12'000 | 12000 | clean |
980.25 | 980.25 | clean |
1'000'000.75 | 1000000.75 | clean |
45.00 | 45.00 | clean |
Column type: NUMBER
| In the file | Cleaned | Status |
|---|---|---|
1.234,50 | 1234.50 | clean |
12.000,00 | 12000.00 | clean |
980,25 | 980.25 | clean |
1.000.000,75 | 1000000.75 | clean |
45,00 | 45.00 | clean |
The separators are read from the column: which character groups the thousands and which one starts the decimals. Every cell comes back as a plain number with a dot for the decimals and no grouping, ready to sum.
https://mcp.data-cleaner.com/mcp, then ask it to clean your file.curl -X POST https://data-cleaner.com/clean/v1/column \
-H 'Content-Type: application/json' \
-d '{"values": ["1'\''234.50", "12'\''000", "980.25", "1'\''000'\''000.75", "45.00"]}'
The answers above are the API's own, captured when this page was built.