Numbers Stored as Text: Why SUM Returns Zero

Two things have to go right here and only the first one is visible. Reading the digits off a picture is a recognition problem, and when it fails you can see that it failed. Landing those digits in a cell as a number is a parsing problem, and when that fails the sheet looks entirely normal.

How the digits got there does not matter here. Excel’s own Data → From Picture, a phone camera, a paste from a PDF, or your own typing all end at the same place: a string arriving in a cell. What that importer does to long numbers and leading zeros is a separate subject, worked through in Image to Excel and the traps in Data from Picture. This is about the step after it.

What follows is the half nobody documents: what happens between a paste and a cell.

Why a correctly-read number lands as text

A spreadsheet does not store what you paste. It parses it, decides what kind of thing it is, and stores that. The parse runs against your locale — set in Windows or macOS, or in Google Sheets under File → Settings. If the string does not look like what that locale calls a number, the cell quietly falls back to text.

Text is not a cosmetic problem. SUM skips it. AVERAGE skips it. A pivot counts it instead of adding it. The column looks right, the total is wrong, and nothing warns you.

Three things push a string over that line, and all three arrive from photographs more often than from typing.

• A grouping separator that the locale does not expect. 1,284.50 parses in a US or UK sheet and fails in a German one. 1.284,50 does the reverse.
• A character your eye skips. A currency symbol, a trailing space, a non-breaking space that came across from a PDF, a Unicode minus − where a hyphen - belongs.
• A misread glyph. This is where recognition and parsing meet. Engines confuse 1 with l, I and the pipe |, because in a great many typefaces those are the same few pixels — why OCR gets numbers wrong has the full confusion table. One stray l inside a figure turns a number into text without changing anything you would notice at a glance.

The separator decides everything

1,234 is not one number. In Chicago it is one thousand two hundred and thirty-four. In Hamburg it is one and a bit. Nothing in the string settles it, and any tool reading it has to guess from context it does not have.

The error that follows is a clean factor of a thousand in one direction or the other, which is exactly the magnitude that survives a glance down the column and dies in an audit. It is a big enough problem to have its own piece: European and US number formats covers which countries write which way, and the rule a scanner can actually apply.

The practical move is to get separators out of the string before it reaches a cell. Grouping marks are decoration, not data. 1284.50 parses as a number in a US-locale sheet and 1284,50 in a German one — strip the grouping and you are down to one variable instead of two.

Proving that a column actually summed

Once a few dozen values are in, you cannot eyeball this. Use the sheet to tell you.

Symptom	What it means	The check that proves it
Values sit against the left edge of the cell	Stored as text — numbers right-align under General format	=ISNUMBER(A2) returns FALSE
SUM over the column returns 0	Every value is text, and SUM ignores text	=COUNT(A2:A200) returns 0 while =COUNTA(A2:A200) counts them all
The total is plausible but slightly low	A mixed column — the dangerous case, because it looks fine	The gap between COUNTA and COUNT is the number of text rows
The total is out by a factor of exactly 1,000	A grouping separator was read as a decimal point, or the reverse	Sort ascending and look at both ends of the column
A cell reads 3.56938E+14	A number, but too wide for the column, so Excel switched to scientific	Widen the column; the value itself is intact
A small green triangle in the cell corner	Excel’s own “number stored as text” flag	Select the range, open the warning menu, Convert to Number

The mixed column is the row to take seriously. An all-text column announces itself the moment you try to add it up. A column where nine values in ten parsed and the tenth did not produces a total that is wrong by one row’s worth — small enough to look like a rounding difference and large enough to matter.

Repairing a column that arrived as text

Three routes, in increasing order of effort.

Text to Columns. Select the column, Data → Text to Columns, Delimited, then Next → Next → Advanced. That dialog is the one people never find: it lets you declare what the decimal and thousands separators in the data are, independently of your system settings. Set them to match the source document, finish, and the column reparses. This is the correct tool for a European-format column landing in a US-locale sheet.

A formula. For a one-off, =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")) turns 1.284,50 into 1284.5. Read it right to left: kill the grouping dots, promote the comma to a decimal point, then parse. It works the same way in Google Sheets. Swap the two substitutions round to go the other way.

Change the sheet’s own separators. In Excel, File → Options → Advanced, clear Use system separators, and set them by hand. In Google Sheets it is the file locale under File → Settings. This changes how the whole workbook parses and displays, so it is the right answer when the entire file is in one foreign format and the wrong answer when only one column is.

None of these recover anything that was destroyed at parse time. Format columns as Text before you paste anything long — a card number, a 15-digit IMEI, an asset tag with leading zeros — because once the cell has parsed it, the precision is gone.

Doing less damage at the capture end

The cheapest fix is to hand the spreadsheet a string it cannot misread. That means digits, one decimal separator, and nothing else: no currency symbol, no unit, no grouping mark, no label that drifted in from the line above.

This is what a digits-only reader is for. Our number extraction tool restricts the recognition alphabet to numerals, so the surrounding text never enters the output and cannot be mistaken for part of a figure. On the app side, SwipeScan’s number mode goes a step further: it removes thousands separators outright and normalises the decimal separator to whichever you have set — dot, comma, or left exactly as found — so the value that lands on your clipboard already matches the sheet it is going into.

One thing that has to survive any such cleanup is the sign. A dropped minus does not produce an obviously wrong figure — it produces a plausible one, wrong by twice its own value, in a column of charges and refunds that then reconciles against nothing. A trailing percent matters for the same reason: it changes what the number means rather than what it is worth. The number mode above keeps both, validating what it outputs against a pattern that permits a leading + or - and a trailing % and nothing else, so a credit line on a statement stays a credit.

None of this is visible while it is happening. A column of text looks exactly like a column of numbers, and the sheet only tells you when a total comes back as zero — or worse, does not, because half the column parsed and half did not.

The short version

• Reading the digits and landing them as a number are two different jobs; only the first one fails visibly.
• A spreadsheet parses every paste against its locale, and anything that does not match becomes text — which SUM then ignores.
• Grouping separators are the usual cause. Strip them before the paste rather than repairing the column after.
• COUNT against COUNTA is the fastest proof that a column is really numeric; a gap between them is the count of broken rows.
• Text to Columns → Advanced lets you declare the source document’s separators, which is the correct repair for a foreign-format column.