Troubleshooting “TypeError” when performing math on string-formatted Excel cells.

A TypeError: unsupported operand type(s) in Python in Excel means your code is attempting a mathematical operation (such as addition, subtraction, or division) on a cell whose data type is recognized as text (str) rather than a number (int or float).

Standard Excel formulas perform automatic type coercion—adding ="10" and =5 yields 15. Python does not do this. If cell A1 contains the text string "100" and cell B1 contains the number 50, running xl("A1") + xl("B1") will fail with an immediate TypeError.

Quick Fix: How to convert string columns to numbers in Python

Convert the text column into numeric values using pd.to_numeric() with errors='coerce'. This strips text formatting and transforms the column into numbers that Python can calculate:

df["Price"] = pd.to_numeric(df["Price"].astype(str).str.replace("$", "").str.replace(",", ""), errors="coerce")

---

Why Text-Formatted Numbers Break Python in Excel

Excel allows numbers to exist in multiple internal formats: true numbers, text strings, or formatted accounting displays.

How Operations Behave in Excel vs. Python:

Expression: Value1 (Text "50") + Value2 (Number 10)
┌─────────────────────────────────┬──────────────────────────────────────────┐
│ Environment                     │ Behavior / Outcome                       │
├─────────────────────────────────┼──────────────────────────────────────────┤
│ Standard Excel Formula (=A1+B1) │ Automatically coerces text to 50 + 10=60 │
│ Python (=PY): val1 + val2       │ Crashes: TypeError: unsupported operand  │
│ Python (=PY): text_val * 3      │ Replicates string: "505050" (Not 150!)   │
└─────────────────────────────────┴──────────────────────────────────────────┘

The most common causes of this mismatch include:

1. Numbers stored as text: Values imported from CSVs, external accounting software, or databases with leading apostrophes (e.g., '1500).
2. Hardcoded formatting symbols: Cells containing currency signs ($, €), thousand separators (,), or percent signs (%) that are entered as literal text rather than applied through cell formatting.
3. Unexpected string multiplication: In Python, multiplying a string by an integer does not do math; it repeats the text (e.g., "20" * 2 becomes "2020"). If you multiply a string by a float (e.g., "20" * 1.5), Python throws TypeError: can't multiply sequence by non-int of type 'float'.

If your sheet has lookup issues related to data formatting, see #N/A when searching Numbers stored as Text.

---

Step-by-Step Diagnostic & Cleaning Workflow

Follow this procedure to locate text-formatted cells and clean them for mathematical calculations.

Encounter TypeError during Math Operation
                  │
                  ▼
Inspect Column Types: Run df.dtypes
                  │
        ┌─────────┴─────────┐
     dtype: object       dtype: float64/int64
        │                   │
        │                   ▼
        │             Check for None/NaN (See ValueError guide)
        ▼
Does column contain currency symbols, commas, or spaces?
        │
   ┌────┴────┐
  YES        NO
   │         │
   │         ▼
   │     Run simple conversion:
   │     df["Col"] = pd.to_numeric(df["Col"], errors="coerce")
   ▼
Clean symbols before conversion:
df["Col"] = df["Col"].astype(str).str.replace(r"[$, ]", "", regex=True)
df["Col"] = pd.to_numeric(df["Col"], errors="coerce")

Step 1: Identify Which Columns Are Stored as Text

In an empty Python cell, check how Pandas categorizes your table:

df = xl("InvoiceTable[#All]", headers=True)
df.dtypes

If a column you expect to be numeric is listed as object, Pandas is treating the entries as text strings.

---

Step 2: Clean and Convert the Data

Scenario A: Clean Numeric Text (No Symbols)

If the cells contain standard digits stored as text strings (e.g., "100", "250.75"):

import pandas as pd

df = xl("SalesData[#All]", headers=True)

# Convert single column to float
df["Revenue"] = pd.to_numeric(df["Revenue"], errors="coerce")

# Perform calculation
df["Tax"] = df["Revenue"] * 0.08
df

Scenario B: Numbers Containing Currency Signs, Commas, or Spaces

If your cells contain literal strings like "$1,450.00":

import pandas as pd

df = xl("BudgetTable[#All]", headers=True)

# Remove dollar signs, commas, and trailing spaces
clean_series = df["Budget"].astype(str).str.replace("$", "", regex=False)
clean_series = clean_series.str.replace(",", "", regex=False).str.strip()

# Convert cleaned text to floating-point numbers
df["Budget"] = pd.to_numeric(clean_series, errors="coerce")

# Safe to run math operations
df["Quarterly"] = df["Budget"] / 4
df

Scenario C: Converting Percentage Strings to Decimals

Cells containing literal strings like "15%" must have the percent sign stripped and the resulting number divided by 100:

import pandas as pd

df = xl("Rates[#All]", headers=True)

# Remove percent sign, convert to float, divide by 100
df["Rate"] = pd.to_numeric(df["Rate"].astype(str).str.replace("%", "", regex=False), errors="coerce") / 100

# Math executes properly
df["Interest"] = df["Principal"] * df["Rate"]
df

---

Fixing Cell Formatting in the Spreadsheet

Rather than writing extensive regex and replacement chains in Python, you can fix the issue at the source using Excel’s native tools:

1. Text to Columns: Select the problematic column, go to the Data tab, click Text to Columns, and immediately click Finish. Excel re-evaluates the cells as numbers.
2. Proper Cell Formatting: Do not manually type currency symbols or percent signs into cells. Enter raw numbers (e.g., 1250.50), then format them using Home > Number Format (Currency, Accounting, or Percentage). Excel will display the symbols visually while passing clean numeric values directly to Python.

If you encounter errors when converting ranges into one-dimensional series, review Solving “Type Mismatch” when passing Excel Ranges to Python Lists..

When working with ranges containing upstream lookup breaks, consult Fixing “ValueError” when Python encounters #N/A or #VALUE! in an input range..

If applying Python outputs strips away your preferred number displays in adjacent cells, see Fixing “Format Loss” when Python overwrites existing cell formatting..

For deeper coverage of data type handling and syntax debugging in Python in Excel, visit Managing Python Libraries in Excel: Fixing Imports and Syntax..