Fix "Could Not Convert String to Float" in Excel Data

ValueError: could not convert string to float: '1,234.50' is one of the most common errors in Excel data work. It appears when a column that looks numeric in Excel turns out to contain text — usually because the data was typed, pasted from another system or exported as formatted strings. astype(float) stops at the first bad value and names only that one, so fixing it one error at a time is slow, and the tempting shortcut, pd.to_numeric(errors="coerce"), makes the error go away by turning every problem into NaN — including values that were perfectly good amounts written as € 1.234,50. This page finds every failing cell at once, groups the failures by cause, cleans the common formats deliberately, and reports what is still unconvertible so it can be fixed at the source rather than silently lost. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl

The sample is an invoice export whose Amount column mixes real numbers with text in a dozen formats — the state such columns are usually in when several people and systems have written to them.

Step 1 — Reproduce, then find every failing value

import pandas as pd

df = pd.read_excel("invoices.xlsx", keep_default_na=False)
try:
    df["Amount"].astype(float)
except ValueError as e:
    print("ValueError:", e)

converted = pd.to_numeric(df["Amount"], errors="coerce")
bad = df.loc[converted.isna(), ["Invoice", "Amount"]]
print(f"{len(bad)} of {len(df)} values fail:")
print(bad.assign(repr=bad["Amount"].map(repr)).drop(columns="Amount").to_string(index=False))

astype(float) reports the first failure only. Eleven of the fifteen values fail; the other four are already numbers, or text that to_numeric can read as it stands — ' 310 ' with its surrounding spaces and '3.2e3' in scientific notation. Comparing with pd.to_numeric(..., errors="coerce") lists them all — every value that comes back as NaN while the original was not empty. Printing the repr of each value matters, because it shows characters that are invisible in normal output: '2\xa0500' contains a non-breaking space, and '−12.75' starts with the Unicode minus sign − rather than an ASCII hyphen, which looks identical on screen. keep_default_na=False stops pandas from quietly turning "N/A" into NaN on reading, so placeholder text is visible as text — otherwise it would never appear in the list.

Step 2 — Group the failures by cause

import re

def cause(v):
    s = str(v)
    if s.strip().upper() in {"N/A", "NA", "TBC", "-", ""}:
        return "placeholder"
    if "\xa0" in s or " " in s:
        return "non-breaking space"
    if "−" in s:
        return "unicode minus"
    if re.fullmatch(r"\s*\(.*\)\s*", s):
        return "accounting negative"
    if s.strip().endswith("%"):
        return "percentage"
    if re.search(r"[€$£]", s):
        return "currency symbol"
    if re.fullmatch(r"\s*-?\d{1,3}(\.\d{3})+,\d+\s*", s):
        return "decimal comma"
    if "," in s:
        return "thousands separator"
    return "other"

print(bad["Amount"].map(cause).value_counts().to_string())

Most failures fall into a handful of causes, and each has its own correct fix — which is why a single str.replace(",", "") is dangerous: applied to 1.234,56, a European-format amount, it produces 1.23456. Classifying first shows what the column actually contains; on real data, print a few examples of each cause before deciding how to treat it.

Step 3 — Clean deliberately, and report the rest

def to_number(v):
    if isinstance(v, (int, float)):
        return float(v)
    s = str(v).replace("\xa0", "").replace(" ", "").replace("−", "-").strip()
    if s.upper() in {"N/A", "NA", "TBC", "-", ""}:
        return None
    negative = s.startswith("(") and s.endswith(")")
    s = s.strip("()").replace("€", "").replace("$", "").replace("£", "").strip()
    percent = s.endswith("%")
    s = s.rstrip("%").strip()
    if re.fullmatch(r"-?\d{1,3}(\.\d{3})+,\d+", s):        # 1.234,56  ->  1234.56
        s = s.replace(".", "").replace(",", ".")
    s = s.replace(",", "").replace(" ", "")
    try:
        x = float(s)
    except ValueError:
        return "unparsed"
    x = -x if negative else x
    return x / 100 if percent else x

cleaned = df["Amount"].map(to_number)
unparsed = df.loc[cleaned.eq("unparsed"), "Invoice"].tolist()
df["Amount (number)"] = pd.to_numeric(cleaned.where(cleaned.ne("unparsed")), errors="coerce")
print(df.assign(original=df["Amount"].map(repr))[["Invoice", "original", "Amount (number)"]].to_string(index=False))
print("placeholders -> missing:", int(df["Amount (number)"].isna().sum()), "| still unparsed:", unparsed or "none")

Each rule is explicit and in a deliberate order: invisible characters are normalised first, so the later rules see plain text; placeholders become true missing values; parentheses and currency symbols are stripped, remembering the sign; the European pattern — dots for thousands and a comma for decimals — is recognised before ordinary commas are removed, so 1.234,56 becomes 1,234.56 rather than 1.23456; percentages are divided by 100. Scientific notation such as 3.2e3 needs no special handling, because float() understands it. Anything the rules do not recognise is marked "unparsed" rather than silently turned into NaN, so a new format appearing next month shows up in the report instead of becoming a missing amount. Here every value converts, and the three placeholders become missing values — which is different from zero and should stay that way.

Where these values come from

Knowing the origin of each format helps decide whether to clean it or fix it at the source. Thousands separators and currency symbols usually mean a system exported formatted values instead of raw numbers, or someone pasted from a web page or PDF. Non-breaking spaces arrive with copy and paste from web pages and from French or other locales that group digits with a narrow space. The Unicode minus sign comes from word processors and some reporting tools that "beautify" negative numbers. Parentheses for negatives are the accounting convention, typed by hand or exported from finance systems. Decimal commas mean the data passed through a machine set to a European locale. And "N/A", "TBC" and "-" are people filling a required cell with a placeholder. Each of these says something about the process that produced the file, and a short note on each to the file's owner often removes the problem for good.

Keep the original next to the result

Write the cleaned values to a new column, as above, rather than overwriting the original — at least until the cleaning has been checked. Having both side by side makes review easy: sort by the original, filter to rows where the original was text, and confirm each conversion at a glance. It also leaves an audit trail when the converted figures go into a report, so a question such as "why is invoice 7 1,234.56 and not 1.23?" can be answered from the file itself. Once the rules have been reviewed, keep them in one function shared by every script that reads the column, and add a test with one example of each format, so a later change to the rules cannot quietly break a case that used to work. Values that remain unparsed belong in a short exception list sent back to whoever maintains the source.

Prevent it upstream

The cleaning function is a repair; the lasting fix is upstream. If the column comes from a form or template people fill in, restrict it with data validation so only numbers can be entered, as in Add Dropdown Data Validation to Excel with openpyxl. If it comes from another system's export, ask for raw numbers rather than formatted text — most systems can do both. And if it comes from CSV, read it with the right thousands and decimal arguments, which handle the separators during parsing. The specific case of percentages stored as text is covered in Convert Percentage Strings Like "12.5%" to Numbers with pandas.

Common pitfalls

• Fixing one error at a time. List every failing value with to_numeric(errors="coerce").
• errors="coerce" as the fix. It hides good values written in unexpected formats.
• Removing all commas. It breaks European decimal commas.
• Invisible characters. Print repr() to see non-breaking spaces and Unicode minus signs.
• Placeholders as zero. "N/A" is missing, not 0.

Conclusion

"Could not convert string to float" means the column contains text. Find every failing value at once by comparing with pd.to_numeric(errors="coerce"), look at their repr to reveal hidden characters, group them by cause, and convert with explicit rules applied in order — invisible characters, placeholders, signs and symbols, decimal commas, separators, percentages. Mark anything unrecognised instead of turning it into NaN, and fix the source so the problem does not return.

Frequently asked questions

How do I fix "could not convert string to float" in pandas? Find the failing values with pd.to_numeric(column, errors="coerce") compared with the original, see why they fail, and clean them with explicit rules before converting.

How do I find which values cannot be converted to numbers? Select rows where pd.to_numeric(column, errors="coerce") is NaN but the original value is not empty, and print the repr of each value.

Why does a number that looks fine fail to convert? It probably contains an invisible character such as a non-breaking space or a Unicode minus sign; repr() shows them.

How do I convert numbers with a decimal comma like 1.234,56? Remove the dots used as thousands separators and replace the comma with a dot before converting, or read CSV files with thousands="." and decimal=",".

Related

• Up one level: Troubleshooting Common Python Excel Errors — fixes for common errors.
• Fix Number Stored as Text Warnings in Excel Output — the same problem on the writing side.
• Parse Numbers with Units in Excel Cells — values like "12 kg".
• Remove Accents and Normalise Unicode in Excel Text — more on invisible characters.