Scanned numbers won't add up? Fix “numbers stored as text”

By Wiktor Tendera, developer of QuickScan · Updated 14 October 2026

Short answer

Numbers that sit on the left of the cell, show a small green triangle and make SUM return 0 are stored as text. Select them and use the warning icon › Convert to Number, or Data › Text to Columns › Finish for a whole column. If some cells still won't convert, they contain a hidden space, a currency sign or a decimal comma — use =NUMBERVALUE(A2, ",", ".") with the separators the text uses.

How to spot numbers stored as text

They're aligned to the left (real numbers align right, unless someone changed the alignment).
Excel shows a small green triangle in the corner; the warning says Number Stored as Text.
SUM returns 0 or less than expected, and sorting puts 100 before 20.
=ISTEXT(A2) returns TRUE.

Why it happens

Text gets into number columns whenever values arrive as text and Excel doesn't (or can't) convert them: pasting from a web page, a PDF or a photo (Live Text), opening some CSV files, data exported by other systems, or a column formatted as Text before the values were entered. Scanned and copied tables are a classic source — and so is any value with something extra around the digits.

Fix 1: Convert to Number

Select the cells (or the whole column), click the warning icon next to the selection and choose Convert to Number. Quick for one column; the icon only appears when Excel recognizes the text as a number.

Fix 2: Text to Columns

Select one column, choose Data › Text to Columns and click Finish straight away. Excel re-reads every cell as if it were typed in, turning number-like text into numbers. Works for a whole column in one go.

Fix 3: a formula

=VALUE(A2) — converts text in your region's format.
=NUMBERVALUE(A2, ",", ".") — converts text written with a decimal comma and dot grouping (1.234,56), whatever your region is. Swap the separators for 1,234.56.
=VALUE(SUBSTITUTE(A2, CHAR(160), "")) — removes non-breaking spaces (common in text copied from web pages and PDFs) first.
=A2*1 or =--A2 — quick coercion when the text is already in your format.

Copy the results and use Paste Special › Values over the original column if you don't want to keep the formulas.

Cells that won't convert

Currency signs and units: €12.50, 12 kg. Remove them with Find and Replace (replace € with nothing), then convert.
The other decimal separator: 4,50 in an Excel that expects 4.50. Use NUMBERVALUE as above, or see decimal comma vs decimal point.
Hidden characters: leading or trailing spaces, non-breaking spaces. =LEN(A2) longer than the digits you see gives them away.
Codes that should stay text: SKUs, barcodes and postcodes — leave them as text, or the zeros disappear (why).

In Numbers and Google Sheets

Numbers: select the cells, open Format › Cell and choose Number; values with extra characters need those removed first. Google Sheets: Format › Number › Number, or =VALUE(A2); Google Sheets also has Data › Split text to columns.

What QuickScan does

QuickScan's Excel files store plain quantities and amounts as real numbers, shown with the decimals they had on paper — so SUM works on a scanned invoice or statement straight away. What isn't clearly a number stays text on purpose: amounts with a currency sign or unit, percentages, dates and times, codes with leading zeros, long codes, and a value like 1.500 when nothing else in the table says whether the dot is a decimal point. Those are the cells to convert with the fixes above, if you need to calculate with them.

Download on the App Store

On a computer? Point your iPhone's camera at the code to open QuickScan in the App Store.

3 free exports, then a one-time unlock. No subscription.

Questions

Why does SUM return 0?

SUM skips cells that hold text, and a number stored as text is text. Convert the column to real numbers (Convert to Number, Text to Columns, or VALUE) and SUM works.

How do I convert a whole column of text to numbers?

Select the column, then Data › Text to Columns › Finish. Excel re-reads every cell and turns those that look like numbers into numbers, using your region's decimal separator.

Why won't some cells convert?

Usually a hidden character (a non-breaking space from a web page or PDF), a currency sign or unit typed into the cell, or a decimal comma where your Excel expects a point. Remove the extra characters or use NUMBERVALUE with the separators the text uses.

Does QuickScan export numbers as text?

Its Excel files store plain quantities and amounts as numbers. Cells that aren't clearly numbers — codes with leading zeros, long codes, dates, amounts with a currency sign or unit — stay text on purpose, so nothing is guessed.