Numbers stored as text

1 / 4

These quantities came from an export as text. See how they sit on the left of the cell, and SUM in F1 gives 0.

keyboard Typing formulas is easiest on a computer with a keyboard.

text, not numbers

SUM skips text

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How VALUE works

What is a number stored as text?

A cell that shows digits, but that Excel treats as text. It happens with data copied from websites, exported from other systems, or typed with an apostrophe in front ('42).
How can you tell?
- It sits on the left of the cell. Real numbers sit on the right.
- Excel shows a small green triangle in the corner.
- SUM skips it, so totals come out too small, often 0.
- ISNUMBER says FALSE and ISTEXT says TRUE.
Why does it matter for lookups?

Excel treats "42" and 42 as different values, so a VLOOKUP or XLOOKUP for 42 doesn't find "42" and shows #N/A, even though they look the same.
How do you fix it?

VALUE turns text that looks like a number into a real number:
=VALUE(B2)
Any maths does the same, which is why you'll also see =B2*1 or =--B2. To go the other way and turn a number into text, join an empty string: =B2&"".

Know someone fighting with a spreadsheet? Send them this page.