"Number stored as text"
A small green triangle in the corner of the cell: the number is written as text. It displays fine, but SUM ignores it, sorts put it in the wrong place and VLOOKUP cannot find it.
What the message says
The cell contains digits, but Excel treats them as text. It flags it because it is almost always unintended.
Why it appears
- A CSV import or a software export: numbers arrive as text, with spaces, thousands separators or a "$".
- Numbers in the European format (1.234,50) in an English Excel, or the reverse.
- A cell formatted as Text before typing.
- A leading apostrophe ("'1024"), sometimes on purpose to keep a leading zero.
Making it go away
- Select the range, click the warning diamond, Convert to Number: works on a small clean range.
- Data, Text to Columns, Finish: reads the whole column again.
- For codes that must stay text (postcodes, account numbers), ignore the warning.
The tool that handles it
Convert text to numbers converts every column at once, with English and European formats, spaces, symbols and trailing signs, and leaves codes with leading zeros as text. For those, see Leading zeros and long numbers.
Frequently asked questions
- Why does SUM give a wrong total?
- SUM silently ignores text cells: the total only counts real numbers. A +, on the other hand, would return #VALUE!.