Amounts carry their currency as a symbol before or after the number, or as a code, with the separators of the writer's country. To sum them or convert them, the currency and the number have to be separated, and the number read with the right separators.
Column type: CURRENCY
| In the file | Cleaned | Status |
|---|---|---|
1.234,50 € | EUR 1234.50 | clean |
€ 99,90 | EUR 99.90 | clean |
12,00 EUR | EUR 12.00 | clean |
EUR 1.000,00 | EUR 1000.00 | clean |
0,99 € | EUR 0.99 | clean |
Column type: CURRENCY
| In the file | Cleaned | Status |
|---|---|---|
$1,200.00 | USD 1200.00 | ambiguous: '$' stands for several currencies; read as USD |
$35.50 | USD 35.50 | ambiguous: '$' stands for several currencies; read as USD |
USD 980 | USD 980 | clean |
$12,000 | USD 12000 | ambiguous: '$' stands for several currencies; read as USD |
$0.99 | USD 0.99 | ambiguous: '$' stands for several currencies; read as USD |
Every amount becomes an ISO 4217 code and a plain number (EUR 1234.50). A symbol that several currencies use, such as $, is read as its most common currency and the cell says so (is_ambiguous), so a column of Canadian or Australian dollars can be told apart and corrected.
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 €", "€ 99,90", "12,00 EUR", "EUR 1.000,00", "0,99 €"]}'
The answers above are the API's own, captured when this page was built.