How to Fix Numbers Stored as Text in CSV Files

A practical guide — Excel, Google Sheets, Python, and the automatic way.

The cruelest CSV bug is the one that looks like nothing. The column is full of numbers. 1,240, 97, 8.50 — all sitting there, all text. Excel left-aligns them and, if you're lucky, shows the little green triangle. If you're not lucky, you find out when SUM returns 0, FILTER skips half your rows, or a VLOOKUP quietly matches nothing. The numbers were never numbers; they were impostors.

The usual suspects behind text-numbers: invisible whitespace around the value (the #1 cause), a leading apostrophe '97 that Excel uses as a "treat as text" marker, thousands separators or currency symbols from the exporting system, and non-breaking spaces pasted in from a web page. Here's how to convert them back.

Method 1: Excel

  1. The green-triangle fix. Select the column. Click the warning icon that appears and choose Convert to Number. Works when the only problem is the format flag — fast, but it won't touch stray spaces or apostrophes.
  2. Text to Columns (the reliable one). Select the column, then go to Data → Text to Columns → Finish (you don't need to change any settings — clicking Finish re-parses the whole column as numbers). This strips leading apostrophes and stray spaces as a side effect.
  3. Multiply by 1. Type 1 in any empty cell, copy it, select your text-numbers, then Paste Special → Multiply → OK. Multiplying forces Excel to evaluate each cell numerically. Then format the range as Number with the decimals you want.
  4. The formula route. In a helper column use =VALUE(A2), or =NUMBERVALUE(A2, ".", ",") when the export uses a different decimal/thousands separator. Copy it down, then paste-as-values over the original.
Watch out: before converting, check for non-numeric hitchhikers — cells like "N/A", "—", or "TBD" sprinkled through the column. The bulk methods will throw errors on those rows; find them first with a filter, decide what they mean, and clean them separately.

Method 2: Google Sheets

  1. Format first. Select the column and go to Format → Number → Number. Sheets will convert clean text-numbers — but like Excel's green-triangle fix, it gives up on stray spaces.
  2. Strip the spaces, then format. Use Edit → Find and replace, check "Search using regular expressions", find ^\s+|\s+$ and replace with nothing, then apply the Number format. For spaces inside the value, also run one pass replacing plain \s+ with nothing (safe only if your numbers have no legitimate internal spaces).
  3. The formula route. In a helper column use =VALUE(TRIM(A2)) — the TRIM kills the spaces VALUE can't survive — or wrap the whole column in one shot: =ARRAYFORMULA(VALUE(TRIM(A2:A))). Paste as values when done.
  4. Re-download the corrected sheet as CSV (File → Download → Comma-separated values) so the next person never hits the problem.

Method 3: Python (for the technical)

import csv, re

def to_number(cell):
    cell = cell.strip().replace('\xa0', '')  # nbsp, the invisible one
    if re.fullmatch(r'-?[\d,]*\.?\d+', cell):
        cell = cell.replace(',', '')
        return float(cell) if '.' in cell else int(cell)
    return cell  # not a number: leave it alone and log it

with open('numbers.csv', newline='', encoding='utf-8') as f:
    rows = list(csv.reader(f))

for n, row in enumerate(rows[1:], start=2):
    for i, cell in enumerate(row):
        new = to_number(cell)
        if new != cell and isinstance(new, str):
            print(f"row {n}, col {i}: '{cell}' is not numeric — kept as-is")
        row[i] = new

The regex deliberately leaves anything non-numeric untouched and reports it instead of guessing. "N/A" is data too — silently turning it into 0 would corrupt your sums just as badly as the original text-numbers did.

The one-click way

Most text-numbers start life as messy exports — stray spaces, ragged rows, wrong encoding — and then get worse in every tool they pass through. CSV Cleaner trims every cell, normalizes headers, removes blank rows, and converts the file to clean UTF-8 in one click, so the file Excel receives has nothing to misinterpret. $9 once, yours forever.

See CSV Cleaner

More CSV fixes: remove blank rows · fix headers with spaces · convert to UTF-8 · CSV won't import? · trim extra spaces · fix ragged rows

← All guides