Currency formats from multiple countries: a cleaning guide

The client sells in five countries and exports one file with every order in it. You SUM the Amount column and get a total that's somehow smaller than the German figures alone, which is impossible. The problem is that the German rows store €1.234,56 with a comma for the decimal and a dot for thousands — the exact reverse of the £1,234.56 in the UK rows — so when Excel reads the German values on a UK machine, 1.234,56 becomes either the number 1.234 or plain text, and your total is a blend of correct amounts and silently mangled ones. Multi-country money is the one cleaning job where the same character means opposite things in different rows.

Why the separators, not the symbols, are the real trap

Everyone notices the currency symbols; almost nobody notices the separators. The $, € and £ are easy — strip them and record which was which. The danger is the decimal and thousands convention hiding underneath. In the UK and US, 1,234.56 means one-thousand-plus; in Germany, Brazil and much of Europe, 1.234,56 means the identical amount with the roles of dot and comma swapped. Read one with the other's rules and you don't get an error — you get a wrong number that looks perfectly reasonable, which is far worse.

The example: five rows, four conventions

Here's a real Amount column from a five-country export:

Amount
$1,234.56        (US)   -> 1234.56
€1.234,56        (DE)   -> 1234.56
£999.00          (UK)   -> 999.00
R$ 2.000,00      (BR)   -> 2000.00
₹1,23,456.00     (IN)   -> 123456.00

Five rows, and a single SUBSTITUTE rule can't clean them all, because the German and Brazilian rows need dots removed and commas turned into decimal points, while the US and UK rows need exactly the opposite. The Indian row adds its own twist: the lakh grouping 1,23,456 puts commas in non-standard positions, so any rule that assumes commas appear every three digits will misread it. The only safe path is to clean each row according to its convention. For the European-style rows the fix is =SUBSTITUTE(SUBSTITUTE(D2,".",""),",",".")*1 — strip the thousands dots, swap the decimal comma for a point, coerce to a number. For the dot-decimal rows you just strip the commas: =SUBSTITUTE(D2,",","")*1. Which rule to apply depends on knowing the row's country, which is why step one is always to identify the convention, not to start substituting.

Cleaning multi-country currency, step by step

1. Split currency into its own column. Pull the symbol or code (USD, EUR) out first, so the number column holds only digits and separators.
2. Identify each row's number convention — dot-decimal or comma-decimal. If the file has a country column, use it; if not, the separator pattern usually tells you.
3. Normalize to one canonical format — plain digits with a dot decimal and no thousands separators — applying the right substitution per convention.
4. Convert to a single reporting currency with a dated rate table: amount_gbp = amount × rate[currency], and record which rate and date you used.
5. Reconcile per country. Sum each currency before conversion and check it against the client's regional figures, so a mangled row shows up as a country that doesn't tie out.

Do it in that order and the impossible total resolves into a clean, single-currency column you can actually chart — with the original amounts preserved beside it so nobody has to trust the conversion blind.

Record the rate, or the number is unrepeatable

The step analysts skip is writing down the exchange rate and its date. Convert €1.234,56 to pounds today and again next month at a different rate, and the "same" order shows two different values, which looks like an error to a client even though both are correct. Note the rate source and date in your delivery report — "converted at ECB rates, 7 July 2026" — so the number is reproducible. Multi-country currency isn't hard once you stop treating a dot and a comma as universal; it's just unforgiving of anyone who assumes their own locale is the world's.