Convert Currency Strings Like "$1,234.56" and "(500)" to Numbers with pandas

An amount column that arrives as text is one of the most common reasons a report cannot total. The export shows what the source system displayed — $1,234.56, negatives in accounting brackets like (1,200.00), the currency written in front or behind — and pandas reads each cell as a string. astype(float) fails on the first dollar sign; pd.to_numeric(errors="coerce") "succeeds" by turning every one of them into NaN, which is worse. This page, part of Cleaning Excel Data With Pandas, builds a parser that handles the formats real exports use, keeps the currency as its own column, and reports anything it cannot read.

Prerequisites

pip install pandas openpyxl

ledger.xlsx has eleven invoices whose amounts cover the usual export formats: a dollar amount with a thousands comma, an accounting negative in brackets, a euro amount with a leading minus, a European-formatted amount with a code, a trailing minus as some ERP systems write it, a CR suffix for a credit, a code prefix, a pound sign with a space, n/a, an Indian-style grouped number, and one amount with a word typed in front of it.

Step 1 — See why the obvious conversions fail

import re
import pandas as pd

ledger = pd.read_excel("ledger.xlsx")
try:
    ledger["amount"].astype(float)
except ValueError as exc:
    print("astype:", exc)

print(pd.to_numeric(ledger["amount"], errors="coerce").tolist())

astype(float) stops at $1,234.56. to_numeric with errors="coerce" returns NaN for every value — including the nine that are perfectly good amounts — and nothing warns you. A total over that column would be zero. Coercing is useful as a final step, after cleaning, to catch what is left; used first, it silently discards the data.

Step 2 — Parse each value into a sign, a number and a currency

Handle the formats one feature at a time: pull out the currency (symbol or three-letter code), note whether the amount is negative (leading or trailing minus, brackets, a CR suffix), then decide which separator is the decimal point and strip the other:

SYMBOLS = {"$": "USD", "€": "EUR", "£": "GBP", "¥": "JPY"}

def parse_money(text):
    if pd.isna(text):
        return None, None
    s = str(text).strip()
    currency = None
    for sym, code in SYMBOLS.items():
        if sym in s:
            currency, s = code, s.replace(sym, "")
    m = re.search(r"\b([A-Z]{3})\b", s)
    if m and m.group(1) not in ("CR", "DR"):
        currency, s = m.group(1), s.replace(m.group(1), "")
    negative = False
    if re.search(r"\bCR\b", s):
        negative, s = True, re.sub(r"\bCR\b", "", s)
    s = re.sub(r"\bDR\b", "", s).strip()
    if s.startswith("(") and s.endswith(")"):
        negative, s = True, s[1:-1]
    if s.endswith("-"):
        negative, s = True, s[:-1]
    if s.startswith("-"):
        negative, s = True, s[1:]
    s = s.replace(" ", "")
    if "," in s and "." in s:
        decimal = "," if s.rfind(",") > s.rfind(".") else "."
    elif "," in s and re.fullmatch(r"\d+,\d{2}", s):
        decimal = ","
    else:
        decimal = "."
    thousands = "." if decimal == "," else ","
    s = s.replace(thousands, "").replace(decimal, ".")
    if not re.fullmatch(r"\d+(\.\d+)?", s):
        return None, currency
    value = float(s)
    return (-value if negative else value), currency

for raw in ledger["amount"]:
    print(f"{raw!r:16} -> {parse_money(raw)}")

The order of the checks matters. Currency is removed first so that USD and € do not confuse the sign and separator logic; CR and DR are excluded from the currency codes because they are credit and debit markers, not currencies; and the decimal separator is decided only after everything else has been stripped. The last fullmatch refuses anything that is not a clean number by then — about 300 returns None instead of raising.

Step 3 — Apply it to the column and keep the currency

Apply the parser once and split its result into two columns:

parsed = ledger["amount"].map(parse_money)
ledger["value"] = parsed.str[0].astype("Float64")
ledger["currency"] = parsed.str[1]
print(ledger)
print(ledger.groupby("currency", dropna=False)["value"].sum())

A nullable Float64 column keeps the missing and unparseable rows as <NA> rather than turning it into a float NaN, which makes missing values obvious in the output. Grouping by currency shows why the code column matters: adding dollars to euros gives a number with no meaning. Amounts with no currency marker are grouped under a missing currency; decide per source what they are — usually the company's reporting currency — and fill them explicitly.

Step 4 — Report what did not parse

A parser that quietly returns None for surprises is only safe if someone looks at the surprises. List every value that failed, with its row, before the data goes any further:

failed = ledger[ledger["value"].isna() & ledger["amount"].notna()]
print(f"{len(failed)} amount(s) could not be parsed:")
print(failed[["invoice", "amount"]].to_string(index=False))

Only about 300 is listed. n/a never reached the parser: read_excel treats a handful of strings — n/a, NA, NULL, #N/A and others — as missing by default, so the cell was already NaN when it was read. That is usually helpful, but it means a placeholder vanishes silently; pass keep_default_na=False and your own na_values when you need to see exactly what the sheet said. Anything that does appear in the failed list is a format the parser has not met — a new currency symbol, a value with a note typed after it — and deserves either a rule or a question to whoever owns the export. In a scheduled job, fail the run when the list contains anything other than the known placeholders; Validate Excel Columns Before Import with pandas shows the pattern for whole sheets.

Step 5 — Know the ambiguous case

One format cannot be decided from the text alone: a number with exactly one separator and three digits after it. 1,234 is one thousand two hundred and thirty-four in the United States and one point two three four in Germany; 1.234 is the reverse. The parser above treats a lone comma followed by exactly two digits as a decimal comma (12,50), and everything else with a lone separator as US-style. That is a reasonable default for amounts, which almost always carry cents, but it is a default:

for raw in ["1,234", "1.234", "12,50", "12.50"]:
    print(raw, parse_money(raw))

When a source is known to be European, do not guess — read it with the conventions stated explicitly, as in Read European CSV Exports with Semicolons and Decimal Commas, or pass the decimal separator into the parser as a parameter per source.

Step 6 — Write real numbers back to Excel

Once the values are numbers, write them as numbers and let a number format provide the symbol, so the column sums, sorts and filters properly in Excel:

from openpyxl import load_workbook

out = ledger[["invoice", "value", "currency", "amount"]].rename(columns={"amount": "original_text"})
out.to_excel("ledger_clean.xlsx", index=False)
wb = load_workbook("ledger_clean.xlsx")
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = "#,##0.00;[Red](#,##0.00)"
wb.save("ledger_clean.xlsx")
print(pd.read_excel("ledger_clean.xlsx")["value"].sum())

Keeping the original text in its own column makes the cleaning auditable: anyone who doubts a value can see what the export said. The format shows negatives in red brackets, the same convention the source used, while the cell holds a real negative number. Format Excel Cells as Currency with Python covers currency formats in depth.

Common pitfalls

• to_numeric(errors="coerce") first. It turns every formatted amount into NaN without a word.
• Losing the sign. Brackets, trailing minus signs and CR suffixes all mean negative.
• Treating CR as a currency code. It is a credit marker; exclude it from the code check.
• Summing mixed currencies. Keep a currency column and group by it.
• Guessing on 1,234 versus 1.234. Fix the convention per source when amounts have no decimals.

Performance notes

Series.map with a Python function processes about a few hundred thousand values per second, which is fine for most ledgers. For millions of rows, handle the dominant format with vectorised string methods — str.replace for symbols and separators, str.startswith("(") for brackets — and send only the rows that do not match to the full parser.

Conclusion

Currency text must be cleaned before it can be converted: take the symbol or code into its own column, turn brackets, leading or trailing minus signs and CR suffixes into a negative sign, choose the decimal separator from whichever of comma and dot comes last, and validate that what remains is a plain number. Report every value that fails, fix the convention per source for ambiguous whole numbers, and write real numbers back with a currency number format.

Frequently asked questions

Why does astype(float) fail on my amount column? Because the column holds text such as "$1,234.56". float() cannot parse currency symbols or thousands separators, and it has no idea that "(500)" means minus 500. Clean the text first, then convert.

How do accounting brackets work? Accounting formats show negative amounts in parentheses instead of with a minus sign, so "(1,200.00)" means -1200. When the text is exported, the brackets become part of the value and must be turned back into a sign.

How can I tell a European amount from an American one? In "1.234,56" the comma comes last, so it is the decimal separator; in "1,234.56" the dot comes last. A parser can use whichever separator appears last as the decimal point, as long as amounts always have decimals. For whole numbers such as "1.234", fix the convention per source.

Should I store the currency symbol? Store the currency as a separate code column (USD, EUR, GBP) and the amount as a plain number. Mixed currencies must not be summed together, and a code column makes that visible and easy to group by.

Related

• Up one level: Cleaning Excel Data With Pandas — the full cleaning workflow.
• Convert Excel Text Columns to Numbers with pandas — the general text-to-number problem.
• Convert Percentage Strings Like "12.5%" to Numbers with pandas — the same idea for rates.
• Show Negative Numbers in Red with Parentheses in Excel — the display side of accounting negatives.