Level 3 · Numbers stored as text

Nicole’s total is wrong, and nothing on the sheet says so. No error, no warning, just a smaller number. The cause is the most common data problem in spreadsheets: numbers stored as text.

Why an export does this

A CSV file has no types. Every value is just characters, and the program that reads it has to guess what each one is. Columns that were set to Text, values with a stray space, an apostrophe or a currency sign, systems that quote the amounts someone edited by hand: all of these can leave "141.98" in a cell as text. This sheet is built the same way: the returns portal writes the refunds approved by hand (reason changed_mind) as text, and the rest as numbers.

The tell: alignment

Excel and Google Sheets put numbers on the right edge of a cell and text on the left, unless someone overrode the alignment. Scan column E: E3 (28.99) sits on the right, E2 (28.99) on the left. Same amount, different type. The 0.00 format doesn’t help either: it only applies to numbers, so E14 shows 169.00 because those are the characters in the text, not because it was formatted. Desktop Excel also marks such cells with a small green triangle (“Number stored as text”).

You can ask directly, too: =ISTEXT(E2) is TRUE, =ISNUMBER(E2) is FALSE.

What SUM does with text

Formula	What happens to "28.99" in E2
=SUM(E2:E45)	skipped: ranges in SUM, AVERAGE, MIN, MAX keep only numbers
=E2+E3	converted: arithmetic operators turn numeric text into a number
=COUNT(E2:E45)	not counted: COUNT counts numbers only
=COUNTA(E2:E45)	counted: COUNTA counts every cell that isn’t empty

So =SUM(E2:E45) returns 4,002.58, while the 44 refunds really add up to 5,632.96. The ten text amounts, 1,630.38 in all, simply vanish. That is Nicole’s “about $1,600”.

The fastest check on any column you are about to total:

I3   =COUNTA(E2:E45)-COUNT(E2:E45)

It returns 10 here. On a clean column it returns 0.

Converting with VALUE

VALUE(text) reads numeric text and returns the number:

F2   =VALUE(E2)

F2 shows 28.99, right-aligned, and a real number. On a cell that already holds a number, VALUE returns it unchanged, so one formula works for the whole column. Fill it down, then =SUM(F2:F45) gives 5,632.96.

Two shortcuts do the same job: =E2*1 and =--E2 (two minus signs). Both force arithmetic, and arithmetic converts. VALUE says what you mean, which helps the next person reading the sheet.

VALUE returns #VALUE! when the text isn’t a number at all, like "n/a" or "$1,200 approx". That is useful: it shows you the cells that need a person, not a formula.

Common mistakes

• Changing the format to Number. The cell still holds text; SUM still skips it. Convert the value (VALUE, or in Excel, Data > Text to Columns > Finish on the column).
• Trusting a total without a count. Compare COUNT with the number of rows. 34 numbers in a 44-row column means ten are missing from every sum.
• Fixing it only in the total. A clever one-cell formula can convert and add at once, but the next pivot or chart on column E is still wrong. Convert into a clean column and build on that.
• Regional settings. VALUE("1.234,50") works in Europe and fails in the US. Exports from another country’s system need a SUBSTITUTE first.