Clean Mixed-Currency Amounts into Amount and Currency Columns

Expense claims, supplier price lists and spreadsheets assembled from several countries often hold money as text with its currency attached: €1.234,50, USD 1,200, 1200 GBP, £950, CHF 1'250.00, (45.00) EUR. Nothing can be summed until each value is split into a number and a currency, and each part has traps. Symbols are ambiguous — $ could be US, Canadian or Australian dollars. Decimal separators vary — 1.234,50 is one thousand two hundred and thirty-four euros fifty, not one point two. Thousands separators include apostrophes in Switzerland and spaces in France. Negative amounts arrive with minus signs, trailing minus signs or parentheses. This page parses such a column with pandas into a numeric amount and an ISO 4217 currency code, resolves the dollar sign from context, detects the decimal convention from the shape of each number, converts everything to one reporting currency with a rate table, and lists anything it could not read rather than guessing. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

The sample is an expense export with a claim ID, the claimant's country and an amount typed with its currency in fourteen different ways, including one that is not an amount at all. The country column is what resolves the ambiguous dollar sign.

Step 1 — Recognise the currency

Currency is shown either as an ISO code (USD, EUR) or as a symbol. Codes are unambiguous; symbols map to a code, except $, whose meaning depends on where the claim came from:

import re
import pandas as pd

df = pd.read_excel("expenses.xlsx", dtype={"Amount": str})
SYMBOLS = {"€": "EUR", "£": "GBP", "¥": "JPY", "CHF": "CHF"}
DOLLAR_BY_COUNTRY = {"US": "USD", "CA": "CAD", "AU": "AUD", "NZ": "NZD"}
CODES = {"USD", "EUR", "GBP", "CHF", "JPY", "CAD", "AUD", "NZD", "SEK", "NOK", "DKK"}

def currency_of(text, country):
    code = re.search(r"\b([A-Z]{3})\b", text)
    if code and code.group(1) in CODES:
        return code.group(1), text.replace(code.group(1), " ")
    for symbol, iso in SYMBOLS.items():
        if symbol in text:
            return iso, text.replace(symbol, " ")
    if "$" in text:
        return DOLLAR_BY_COUNTRY.get(country), text.replace("$", " ")
    return None, text

print([currency_of(a, c)[0] for a, c in zip(df["Amount"], df["Country"])])

An ISO code wins if present, so 1,000.5 EUR from a British claimant is euros, not pounds — people abroad often claim in the local currency, which is exactly why the claimant's country cannot simply be used as the currency. The order of the checks matters for the same reason: codes first, then unambiguous symbols, and context only as a last resort. Symbols map to a single code, and $ is resolved by the claimant's country — a Canadian's $310.40 is CAD. When neither a code nor a symbol is present, the currency is unknown and the value goes to review; assuming a default currency silently is how expense reports end up overstated by a factor of 140 when yen are read as dollars.

Step 2 — Read the number, whatever its convention

With the currency removed, what is left is a number written in one of several conventions. The position of the last separator decides which is the decimal mark:

def amount_of(text):
    s = text.strip()
    negative = (s.startswith("(") and s.endswith(")")) or s.startswith("-") or s.endswith("-")
    s = re.sub(r"[()\s'  -]", "", s)
    if not re.fullmatch(r"[\d.,]+", s):
        return None
    last_dot, last_comma = s.rfind("."), s.rfind(",")
    decimal = "," if last_comma > last_dot and len(s) - last_comma - 1 in (1, 2) else "."
    if decimal == ",":
        s = s.replace(".", "").replace(",", ".")
    else:
        s = s.replace(",", "")
    value = float(s)
    return -value if negative else value

def parse(text, country):
    currency, rest = currency_of(str(text), country)
    amount = amount_of(rest)
    status = "ok" if currency and amount is not None else ("unknown currency" if amount is not None else "not an amount")
    return amount, currency, status

parsed = df.apply(lambda r: pd.Series(parse(r["Amount"], r["Country"]), index=["Value", "Currency", "Status"]), axis=1)
result = pd.concat([df, parsed], axis=1)
print(result.to_string(index=False))

Apostrophes, ordinary spaces and the narrow and non-breaking spaces French formatting uses are all thousands separators and are removed. Then the rule: if the last separator is a comma followed by one or two digits, the comma is the decimal mark (1.234,50, 2 450,75, 45,00); otherwise the dot is (1,250.00, 1'250.00) and commas are thousands separators. A value with only a comma and three digits after it, such as ¥120,000, is read as thousands — correct here, and the safer reading for most real data. Parentheses, a leading minus or a trailing minus make the amount negative. Anything left that is not digits and separators — "about 300 dollars" — is not an amount, and is reported as such.

Step 3 — Convert to a reporting currency, and review the rest

Once each row has a value and a code, converting to one currency is a lookup:

RATES_TO_GBP = {"GBP": 1.0, "EUR": 0.85, "USD": 0.76, "CAD": 0.55, "AUD": 0.50, "CHF": 0.94, "JPY": 0.0051}
ok = result[result["Status"] == "ok"].copy()
ok["GBP"] = (ok["Value"] * ok["Currency"].map(RATES_TO_GBP)).round(2)
review = result[result["Status"] != "ok"]
print(ok[["Claim", "Value", "Currency", "GBP"]].to_string(index=False))
print(f"total GBP {ok['GBP'].sum():,.2f} from {len(ok)} claims; {len(review)} for review:")
print(review[["Claim", "Amount", "Status"]].to_string(index=False))

with pd.ExcelWriter("expenses_clean.xlsx", engine="openpyxl") as xw:
    ok.to_excel(xw, sheet_name="Converted", index=False)
    review.to_excel(xw, sheet_name="Review", index=False)
    pd.Series(RATES_TO_GBP, name="Rate to GBP").to_frame().to_excel(xw, sheet_name="Rates")

Thirteen of the fourteen claims convert, totalling £9,946.80, and the one that says "about 300 dollars" goes to review with its reason. Note the yen claim: ¥120,000 is £612, and had the yen been read as dollars it would have added over £90,000 to the total.

Edge cases worth deciding up front

The decimal rule handles the common cases, but some values are genuinely ambiguous, and it is better to decide how to treat them than to discover the choice later. 1,234 with nothing else could be one thousand two hundred and thirty-four or, in a decimal-comma country, one point two three four; the rule reads it as thousands, which matches almost all real amounts, but if your data includes small decimal-comma amounts with three decimals — fuel prices, unit costs — use the claimant's country to decide instead. Currencies without minor units, such as the yen, never have decimals, so ¥1.500 is fifteen hundred yen, and a dot there can safely be treated as a thousands separator. A currency code that is not in your list — a claim in Thai baht — should go to review rather than be dropped, which is why the code set is explicit. And symbols shared by several currencies are not limited to the dollar: kr is Swedish, Norwegian or Danish krone depending on the country, and needs the same treatment as $.

Common pitfalls

• Reading the column as numbers. Text such as "€1.234,50" turns into NaN; read with dtype=str.
• Assuming $ means US dollars. Resolve it from the country or a separate column.
• Removing every comma. It breaks decimal-comma amounts such as "1.234,50".
• Defaulting unknown currencies. Send them to review instead.
• Converting without recording the rates. Write the rates used next to the result.

Conclusion

Split mixed-currency text into an ISO currency code and a numeric amount: take an explicit code if present, map symbols to codes and resolve $ from the claimant's country, strip apostrophe and space separators, decide the decimal mark from the last separator and the digits after it, and apply parentheses or minus signs. Convert to one reporting currency with a recorded rate table, keep the original text, and put anything unreadable on a review sheet with its reason.

Frequently asked questions

How do I split an amount and its currency into two columns in pandas? Search each value for an ISO currency code or a currency symbol, map it to a code, remove it from the text, and parse what remains as a number.

How do I convert amounts like 1.234,50 to numbers? If the last separator is a comma followed by one or two digits, treat it as the decimal mark: remove the dots and replace the comma with a dot before converting.

How can I tell which currency a dollar sign means? Use context, such as the claimant's or supplier's country, to map $ to USD, CAD, AUD or another dollar currency, and flag values where the context is missing.

What should happen to amounts that cannot be parsed? List them on a review sheet with the original text and the reason, rather than converting them to zero or a default currency.

Related

• Up one level: Cleaning Excel Data with pandas — tidying Excel data.
• Fix "Could Not Convert String to Float" in Excel Data — the general number-cleaning case.
• Convert Multi-Currency Transactions with an FX Rate Table — dated exchange rates.
• Format Excel Cells as Currency with Python — showing the results.