Currency amounts: 1.234,50 € and $1,200.00 to a currency code and a number

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 fileCleanedStatus
1.234,50 €EUR 1234.50clean
€ 99,90EUR 99.90clean
12,00 EUREUR 12.00clean
EUR 1.000,00EUR 1000.00clean
0,99 €EUR 0.99clean

Column type: CURRENCY

In the fileCleanedStatus
$1,200.00USD 1200.00ambiguous: '$' stands for several currencies; read as USD
$35.50USD 35.50ambiguous: '$' stands for several currencies; read as USD
USD 980USD 980clean
$12,000USD 12000ambiguous: '$' stands for several currencies; read as USD
$0.99USD 0.99ambiguous: '$' stands for several currencies; read as USD

What happened

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.

Do it yourself

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.

More guides