What Are Numbers Stored as Text?
Excel stores each cell value as one of several data types: number, text, date, boolean, or error. When a number is stored as the text type, it looks identical to a real number on screen — but Excel treats it completely differently.
The consequences are silent and serious:
- SUM ignores text cells —
=SUM(A1:A10)skips any cell in the range that contains a text-formatted number, returning a lower total than expected - VLOOKUP returns #N/A — if your lookup column contains text "1001" and you search for the number 1001, they do not match
- AVERAGE is wrong — the denominator only counts real numbers, so the average is calculated over fewer cells than it should be
- Sorting is wrong — text sorts differently from numbers (text "10" sorts before "9" because "1" < "9" alphabetically)
- Conditional formatting rules fail — rules like "greater than 100" will not trigger on text-formatted numbers
How to Spot Numbers Stored as Text
Method 1: Visual alignment check
Real numbers align to the right of a cell by default. Text aligns to the left. If you have a column of numbers and some are left-aligned while others are right-aligned, the left-aligned ones are likely stored as text.
Method 2: Green triangle warning
Excel often (but not always) shows a small green triangle in the top-left corner of cells containing numbers stored as text. Click the cell and you will see a yellow warning icon with the message "Number Stored as Text". This is the clearest visual signal.
Method 3: The ISNUMBER test
In a blank column next to your data, enter =ISNUMBER(A1) and copy it down. Cells returning FALSE are text — including text-formatted numbers. This is the most reliable programmatic test.
Method 4: Check the cell format
Select the cell and look at the Number Format box on the Home tab. If it says "Text", the cell is formatted as text — any number typed or imported into it will be stored as text.
Why Does This Happen?
The most common causes:
- Data exported from other systems — CSV exports from databases, accounting software, or web tools often export numbers as text strings
- Column pre-formatted as Text — if someone formatted a column as Text before entering data, all values go in as text
- Leading apostrophe — typing
'1000(apostrophe before the number) forces Excel to treat it as text. The apostrophe is invisible in the cell - Copy-pasted from another source — pasting from a PDF, web page, or text file often brings in text-typed numbers
How to Fix Numbers Stored as Text
Fix 1: Convert to Number (green triangle method — fastest for small ranges)
- Select the cells with the green triangle warning
- Click the yellow warning icon that appears
- Click "Convert to Number"
Excel converts all selected cells to real numbers instantly.
Fix 2: Paste Special Multiply (best for large ranges)
- Type the number 1 in any blank cell
- Copy that cell (Ctrl+C)
- Select the range of text-formatted numbers
- Right-click → Paste Special → select Multiply → click OK
Multiplying by 1 forces Excel to treat the values as numbers and converts them in place. The original cell formatting is preserved.
Fix 3: Text to Columns (best for an entire column)
- Select the column of text-formatted numbers
- Go to Data → Text to Columns
- Click Finish immediately (no need to change any settings)
This forces Excel to re-parse the column values as numbers.
Fix 4: VALUE() formula (best when you need to keep originals)
In a new column, enter =VALUE(A1) and copy it down. This converts the text to a real number in a new cell. Once done, copy the new column → Paste Special → Values only → delete the original column.
Fix 5: Change cell format and re-enter (for small ranges)
- Select the cells
- Change the format from Text to General (Home → Number Format dropdown)
- Press Ctrl+H (Find & Replace) → Find
.→ Replace with.→ Replace All
This forces Excel to re-evaluate the cell values under the new format. Note: this trick only works for cells containing decimal numbers.
Preventing Numbers Stored as Text
- Never pre-format columns as "Text" unless you genuinely need text (like ID codes with leading zeros)
- When importing CSV files, use Data → From Text/CSV and set the column type to "Number" in the import wizard
- After pasting data from external sources, run an ISNUMBER check on critical columns before using them in calculations
- Use Excel Tables (Ctrl+T) — tables enforce consistent data types within columns more reliably than plain ranges
Frequently Asked Questions
My SUM returns 0 but the cells show numbers. Is this the cause?
Almost certainly yes. Test with =ISNUMBER(A1) on any of those cells. If it returns FALSE, numbers stored as text is your problem. Use Fix 2 (Paste Special Multiply) to convert the entire range at once.
How do I keep leading zeros in numbers (like 007) without the text problem?
Format the cells as a custom number format: 000 (for 3-digit zero-padded numbers). This stores the real number 7 but displays it as "007". It remains a real number that SUM and other functions will work with correctly.
Will the ExcelErrorFinder tool detect this problem?
Yes — the audit tool flags cells where a numeric-looking value is stored as text type, with exact cell addresses so you can fix them all at once.