Excel SUM wrong? Fix numbers stored as text with Text to Columns

You add up a column, the total looks too small, and you start re-adding the figures one by one to find the mistake. Often there is no arithmetic mistake at all. Some of the "numbers" are text, usually because they were pasted from another system, a PDF or a web page, and SUM skips text without warning.

In our test sheet, adding the figures by hand gave 194,500. =SUM over the same cells gave 4,500.

TL;DR: select the column, go to Data → Text to Columns, and apply. The text turns back into numbers.

How to spot it

Look at the alignment. By default Excel right-aligns numbers and left-aligns text. If figures in an amount column hug the left edge of the cell, they are almost certainly text.

Steps

1. Select the amount cells.
2. On the Data tab, choose Text to Columns.
3. When the pane appears, apply it without changing anything. The values jump to the right side of their cells, and the total updates to 194,500.

This uses Text to Columns for a side effect: re-entering each cell makes Excel parse the content again, and anything that looks like a number becomes one.

We checked this in Excel for the web. In desktop Excel you'd often see a small green triangle on these cells with a Convert to Number option; in the browser we didn't see that indicator, which is why we used Text to Columns.

Common mistakes

• Formatting the cells as Number and expecting a fix. Changing the format doesn't change values that are already text. You need something that re-enters them.
• Selecting more than one column. Text to Columns works on a single column at a time.
• Hidden characters. If some cells still won't convert, they may contain a non-breaking space or a currency symbol. SUBSTITUTE or Find and Replace can clear those first.
• Delimiters set from an earlier run. If a previous Text to Columns split on commas, numbers like 25,000 could be split across cells. Check the settings before applying.

Other ways to convert

• A helper column with =VALUE(B2) or =B2*1 converts one cell at a time, and is easier to audit.
• In pandas, text amounts with thousands separators convert in one line:

df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")

errors="coerce" turns anything that still isn't a number into NaN, so you can filter those rows and see exactly which entries were bad. That's a useful check before trusting any total.

---

Written with AI assistance. The before-and-after totals come from a test sheet in Excel for the web.