Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Convert Excel Text Columns to Numbers with pandas

Numbers stored as text will not sum. Convert them in pandas — currency symbols, thousands separators, European decimals, parentheses negatives, and reporting what failed.

A column of prices comes out of Excel and df["revenue"].sum() returns one enormous string. The values look like numbers, right-aligned even, but they are text — because the export wrote them as text, or because a currency symbol, a thousands separator or a stray space made pandas give up on inference. This guide covers converting them properly: the cleaning steps in the right order, the formats that need special handling, and the reporting that stops a bad conversion from silently understating a total. It is part of Cleaning Excel Data with pandas.

Five text formats, and what each conversion needs Five rows pairing a text value with the handling it requires. A dollar amount with a thousands comma needs the symbol and separator stripped. A European figure with dots for thousands and a comma decimal needs the dots removed and the comma converted, in that order. A parenthesised value is an accounting negative and must be negated after stripping. A percentage must be divided by one hundred. A plain numeric string converts directly. as stored (text) what it needs "$1,234.56" strip the symbol and the comma "1.234,56" remove dots FIRST, then comma to dot "(1,000.00)" accounting negative — strip, then negate "12.5%" strip the sign and divide by 100 "4268.5" to_numeric handles it directly

Prerequisites

Bash
pip install pandas openpyxl

A frame with each of the awkward formats:

Python
import pandas as pd

df = pd.DataFrame({
    "revenue": ["$1,234.56", "1.234,56", "(1,000.00)", "12.5%", "4268.5",
                "n/a", ""],
})
print(df["revenue"].dtype)      # object

Step 1 — Confirm the problem

dtypes is the first thing to look at when arithmetic behaves oddly:

Python
import pandas as pd

df = pd.read_excel("sales.xlsx")
print(df.dtypes)

suspect = [
    name for name in df.columns
    if df[name].dtype == "object"
    and df[name].astype("string").str.match(r"^\s*[\$€£(]?[\d.,\s]+\)?%?\s*$",
                                            na=False).mean() > 0.8
]
print("probably numeric but stored as text:", suspect)

The mean() > 0.8 test asks whether most values in the column look numeric. That is more useful than checking one value, because a genuinely textual column will score near zero while a numeric column with a few n/a entries still scores high. The broader dtype audit is covered in checking Excel data types with pandas.

Step 2 — The simple case

When the values are clean numeric strings, one call does it:

Python
import pandas as pd

df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")

errors="coerce" turns anything unparseable into NaN instead of raising. That is right for a batch job — and dangerous on its own, because a column where half the values failed still sums happily, just to the wrong number.

Step 3 — Clean the formats, in order

Five cleaning steps that must happen in this order A five-stage pipeline. First accounting parentheses are detected and recorded before being stripped, because the information they carry is the sign. Second percentage signs are noted and removed so the value can be scaled afterwards. Third currency symbols and stray characters are stripped. Fourth the decimal convention is resolved — for European numbers the thousands dots must be removed before the comma is converted to a dot, never the reverse. Only then does to_numeric run. 1 parentheses record the sign 2 percent sign note, then strip 3 symbols currency, spaces 4 separators dots BEFORE comma 5 to_numeric coerce reverse steps 1 and 5 and the sign is lost; reverse the two halves of step 4 and the decimal is "1.234,56" → remove dots → "1234,56" → comma to dot → 1234.56

Currency symbols, separators and accounting negatives all need removing before to_numeric sees the value. The order is not arbitrary:

Python
import pandas as pd

def to_number(series, decimal=".", percent_as_fraction=True):
    """Convert a text column of formatted numbers into real numerics."""
    text = series.astype("string").str.strip()

    # Accounting negatives: (1,000.00) means -1000.00
    negative = text.str.match(r"^\(.*\)$", na=False)
    text = text.str.replace(r"^\((.*)\)$", r"\1", regex=True)

    # Percentages: remember which, so we can scale afterwards.
    percent = text.str.endswith("%", na=False)
    text = text.str.rstrip("%")

    # Currency symbols and spaces, including the non-breaking kind.
    text = text.str.replace(r"[^\d.,\-+eE]", "", regex=True)

    if decimal == ",":
        # European: dots are thousands separators, comma is the decimal.
        text = text.str.replace(".", "", regex=False)
        text = text.str.replace(",", ".", regex=False)
    else:
        text = text.str.replace(",", "", regex=False)

    numbers = pd.to_numeric(text, errors="coerce")
    numbers = numbers.where(~negative, -numbers)
    if percent_as_fraction:
        numbers = numbers.where(~percent, numbers / 100)
    return numbers

print(to_number(df["revenue"]).tolist())

The European branch is where order matters most. Removing dots before converting the comma turns 1.234,56 into 1234,56 and then 1234.56. Doing it the other way round produces 1.234.56, which parses as nothing.

Detecting the convention automatically is possible when a column is internally consistent — the last separator in a well-formed number is the decimal one:

Python
import pandas as pd

def detect_decimal(series, sample=200):
    """Guess whether a column uses ',' or '.' as its decimal separator."""
    text = series.astype("string").dropna().head(sample)
    comma_last = text.str.match(r"^[^,]*\d[.\s]\d{3},\d+$", na=False).sum()
    dot_last = text.str.match(r"^[^.]*\d[,\s]\d{3}\.\d+$", na=False).sum()
    return "," if comma_last > dot_last else "."

Treat that as a hint to confirm, not a decision to trust silently — a column of mixed conventions will fool it, and the consequence is values wrong by a factor of a thousand.

Step 4 — Report what failed

The step that separates a safe conversion from a dangerous one:

Three outcomes, only one of which is a problem After coercion, the column divides into three groups. Converted values are the successful majority. Cells that were blank to begin with are expected missing data and simply need counting. Values that held text and failed to parse are the group that matters: they were data, and they are now silently absent from every total. Reporting the distinct failing values usually reveals a small number of causes such as n slash a or a footnote marker. to_numeric errors="coerce" converted — real numbers the expected majority; nothing to do already blank — expected missing count them, decide fill or drop was text, failed — the group that matters report the distinct values: usually "n/a", "-", a footnote marker
Python
import pandas as pd

def convert_and_report(df, column, threshold=0.05, **kwargs):
    """Convert a column to numeric, reporting anything that failed."""
    raw = df[column].astype("string").str.strip()
    converted = to_number(df[column], **kwargs)

    blank = raw.isna() | (raw == "")
    failed = converted.isna() & ~blank

    if failed.any():
        print(f"{column}: {int(failed.sum())} value(s) failed to convert")
        print(raw[failed].value_counts().head(10).to_string())

    rate = failed.mean()
    if rate > threshold:
        raise ValueError(
            f"{column}: {rate:.1%} of values failed to convert — "
            "check the expected format before trusting any total."
        )

    df[column] = converted
    return df

df = convert_and_report(df, "revenue")

Printing the distinct failing values is what makes this actionable. Nine times out of ten the report reads n/a: 34 or -: 12, and the fix is one line added to the cleaning step rather than an investigation.

Step 5 — Choose the right dtype

A column of counts that can be missing should be Int64, not int64:

Python
import pandas as pd

units = pd.Series(["120", "", "88", "n/a"])

as_float = pd.to_numeric(units, errors="coerce")
print(as_float.tolist())        # [120.0, nan, 88.0, nan]

as_int = as_float.astype("Int64")
print(as_int.tolist())          # [120, <NA>, 88, <NA>]

int64 cannot hold a missing value, so pandas promotes the column to float and every integer gains a decimal point — which then writes to Excel as 120.0 and reads badly in a report. Int64 holds both.

For money, the pragmatic choice is float with rounding at the point of presentation, since Excel itself stores doubles:

Python
df["revenue"] = to_number(df["revenue"]).round(2)

Where exact decimal arithmetic genuinely matters — reconciling to the cent across many rows — accumulate with Decimal in Python and convert to float only when writing, because a float sum of many two-decimal values drifts.

Common pitfalls and fixes

SymptomCauseFix
sum() concatenates stringsColumn is object dtypeConvert with to_numeric first.
Total far too lowFailed values coerced to NaN silentlyReport the failures; raise above a threshold.
1.234,56 becomes NaNEuropean format not handledRemove dots first, then comma to dot.
Values off by 1000×Decimal convention detected wronglyConfirm the convention; do not auto-detect blindly.
Negatives came through positiveAccounting parenthesesDetect, strip, then negate.
120 written as 120.0NaN promoted the column to floatUse the Int64 dtype.
12.5% became 12.5Percentage not scaledDivide the percent rows by 100.
Some values still fail after cleaningA character not in the strip patternPrint the failing values and extend it.

Performance and scale notes

to_numeric is fast; the regex cleaning around it is not. Each str.replace walks the column, and the helper above makes six passes.

Clean the distinct values, not every row. A million-row price column typically holds far fewer distinct strings, and the saving is proportional:

Python
import pandas as pd

def to_number_via_lookup(series, **kwargs):
    """Convert each distinct text value once, then map."""
    distinct = pd.Series(series.dropna().unique())
    lookup = dict(zip(distinct, to_number(distinct, **kwargs)))
    return series.map(lookup).astype("float64")

Skip the cleaning when it is not needed. Try the direct conversion first and only fall back to the expensive path for the values that failed:

Python
import pandas as pd

def to_number_fast(series, **kwargs):
    direct = pd.to_numeric(series, errors="coerce")
    if not direct.isna().any():
        return direct                       # nothing needed cleaning

    needs_work = direct.isna() & series.notna()
    cleaned = to_number(series[needs_work], **kwargs)
    return direct.where(~needs_work, cleaned)

On a column that is already numeric this returns after one vectorised pass, which matters when the same function runs across thirty columns of an import.

Push the fix upstream where you can. A column arriving as text usually means the export wrote it that way, and fixing the export removes the whole problem — see exporting SQL query results to Excel for controlling types at the source. Failing that, converting at ingest once is far cheaper than converting on every read, and for large workbooks it composes with the chunked approach in reading large Excel files in chunks.

Conclusion

A numeric column stored as text is a formatting problem with arithmetic consequences. Check dtypes first, then clean in a fixed order: strip accounting parentheses and remember them, note and strip percentage signs, remove currency symbols and separators, and handle the European convention by removing dots before converting the comma. Convert with errors="coerce", then always report which values failed and refuse to continue when too many did. Use Int64 for counts that can be missing, and clean the distinct values rather than every row when the column is large.

Frequently asked questions

Why is my sum concatenating strings instead of adding? The column has object dtype, so the plus operator joins strings. Check df.dtypes — anything that should be numeric and shows object needs converting before any arithmetic.

What does errors="coerce" do to values it cannot parse? It replaces them with NaN rather than raising. That keeps a batch job running, but you must inspect which rows became NaN afterwards, or a data-quality problem turns into a silently understated total.

How do I handle European numbers like 1.234,56? Remove the dots as thousands separators, then replace the comma with a dot before converting. Doing it in the other order destroys the decimal, so the order is not optional.

What about negatives shown in parentheses? Accounting formats write minus one thousand as a parenthesised value. Detect the parentheses, strip them, convert, and negate the result — to_numeric does not understand the convention.

Should I use Int64 or int64?Int64 with a capital I, the nullable integer type, whenever the column can contain a missing value. Plain int64 cannot hold NaN, so pandas silently promotes the column to float and integers gain a decimal point.