Here's a revenue column from a client's export:

revenue 1250.00 £1250.00 "1,250.00" n/a "1.250,50" (120.00) 980.00 1 250 330.10 410.00 

A person reads that and sees roughly £7,850. Convert it the usual way and you get something else:

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

Six of the ten values quietly became NaN, and the total lost well over half its value. Excel does the same thing more politely: SUM skips cells that hold text. Power BI turns them into errors and loads them as blanks. In every case the report still refreshes, so nothing tells you the number is wrong.

Why numbers turn into text

When a client sends a file, these are the usual causes:

  • Currency symbols: £1250.00, often from a "formatted" export
  • Thousands separators: 1,250.00, fine to a human, text to a parser
  • Another country's format: 1.250,50 is one thousand two hundred and fifty in much of Europe
  • Accounting negatives: (120.00) instead of -120
  • Placeholders: n/a, TBC, -
  • Spaces inside the number: 1 250, common when values are copied from a PDF or a web page
  • Percentages: 5% in a column you expected as 5 or 0.05

The fix is different for each, so before you clean anything it's worth knowing which ones you've got.

The script

This reads a CSV, finds the number columns with text hiding in them, and groups the problem values by cause, with row numbers so you can find them in Excel.

import re import sys import pandas as pd # Each pattern is a reason a value isn't a plain number, checked in order. PATTERNS = [ ("placeholder", re.compile(r"^(n/?a|tbc|tbd|none|null|-+|\?+)$", re.I)), ("currency symbol", re.compile(r"^[-(]?[£$€]\s?[\d.,]+\)?$")), ("thousands separator", re.compile(r"^-?\d{1,3}(,\d{3})+(\.\d+)?$")), ("European format", re.compile(r"^-?\d{1,3}(\.\d{3})*,\d+$")), ("brackets for negative", re.compile(r"^\(\d[\d.,]*\)$")), ("percentage", re.compile(r"^-?[\d.]+\s?%$")), ("trailing minus", re.compile(r"^[\d.,]+-$")), ("spaces inside", re.compile(r"^-?\d[\d\s]*\d(\.\d+)?$")), ] def reason(value): for name, pattern in PATTERNS: if pattern.match(value): return name return "other text" def check_column(df, column): values = df[column].str.strip() filled = values[values != ""] numeric = pd.to_numeric(filled, errors="coerce") bad = filled[numeric.isna()] reasons = bad.map(reason) # A text column (names, regions) is mostly "other text". Skip it: it was never meant to be numbers. if bad.empty or (reasons == "other text").sum() > 0.5 * len(filled): return None print(f"\n{column}: {len(bad)} of {len(filled)} values aren't numbers") for why, group in bad.groupby(reasons): rows = ", ".join(f"row {i + 2}: {v!r}" for i, v in group.head(3).items()) print(f" {why} ({len(group)}): {rows}") return len(bad) if __name__ == "__main__": df = pd.read_csv(sys.argv[1], dtype=str, keep_default_na=False) found = [c for c in df.columns if check_column(df, c)] if not found: print("Every number column is clean.") 

Run against the file above (with a few more columns around it):

$ python find_text_numbers.py revenue.csv revenue: 6 of 10 values aren't numbers European format (1): row 6: '1.250,50' brackets for negative (1): row 7: '(120.00)' currency symbol (1): row 3: '£1250.00' placeholder (1): row 5: 'n/a' spaces inside (1): row 9: '1 250' thousands separator (1): row 4: '1,250.00' discount: 1 of 9 values aren't numbers percentage (1): row 2: '5%' 

The customer and region columns don't appear, because they're meant to be text.

A few choices worth explaining

Read everything as text first. dtype=str and keep_default_na=False stop pandas deciding for you. If pandas reads revenue as numbers, n/a silently becomes NaN before you ever see it.

Row numbers match Excel. i + 2 adds one for the header and one because pandas counts from zero, so "row 6" is row 6 when the client opens the file.

Text columns are skipped, not flagged. A column where most values are plain words (names, regions) was never a number column. Without that rule, every customer name would show up as a problem.

Group by cause, not by row. "Six bad values" isn't actionable. "One European-format number, one placeholder, one currency symbol" tells you whether to fix the export settings, ask the client, or clean it yourself.

What to do with the results

  • Formatting causes (currency symbols, separators, spaces): usually an export setting. Ask the client to export raw values, or strip the characters in Power Query.
  • European format: check the whole column uses the same convention before converting. Remove every full stop blindly and a genuine decimal like 1.25 becomes 125.
  • Placeholders: ask what they mean. n/a might mean zero, unknown, or "this order was cancelled", and each changes the total differently.
  • Accounting negatives: convert (120.00) to -120. Deleting the brackets turns a refund into a sale.

Where this falls short

This checks one file on its own. The more useful question is usually "is this worse than last month?" If 0% of values were text last month and 4% are this month, something changed at the source, and that's when to ask the client.

Disclosure: that comparison is what I'm building Veritaproof to do. It's a Windows app that checks each new client file against the ones before it, including how many values in a number column aren't numbers. The script above is yours to use either way.

What's the strangest thing you've found in a column that was supposed to be numbers?