Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Text number error
Excel & Google SheetsWhat Text number means
A cell can contain the characters '123' but have those characters stored as a text string rather than a numeric value. SUM ignores text cells, VLOOKUP may fail to match, and sort order is alphabetical instead of numeric. A green triangle in the top-left corner of the cell is Excel's standard warning for this condition.
Common causes
- Data was imported from CSV, a database export, or a web source that wrapped numbers in quotes, causing the application to store them as text.
- The column was formatted as Text before numbers were typed or pasted, forcing text storage regardless of the characters entered.
- A leading apostrophe was typed before the number (e.g., '42) to prevent auto-formatting, which stores the value as text.
- Numbers were pasted as 'Values only' from a source where they were already text, preserving the text storage type.
- Regional settings mismatch: numbers from a locale that uses commas as decimal separators are pasted into a locale that uses periods, making them unrecognizable as numbers.
Example fix
Broken
=SUM(A2:A10)Fixed
=SUMPRODUCT(VALUE(A2:A10))When A2:A10 contains numbers stored as text, SUM returns 0. SUMPRODUCT with VALUE() coerces each text number to a real number before summing, giving the correct total without needing to fix the source data first.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1For a small range: select the cells, click the green triangle warning, and choose 'Convert to Number' from the dropdown.
- 2Use VALUE() to convert in a helper column: =VALUE(A2). Paste-special the helper column as values over the originals, then delete the helper column.
- 3Multiply by 1 in a helper column: =A2*1. This coerces text to number. Same paste-special workflow as above.
- 4Use the 'Text to Columns' wizard (Excel: Data > Text to Columns > Finish immediately with no changes) — this forces Excel to re-parse the selected cells as numbers.
- 5In Google Sheets: =VALUE(A2) or =A2*1 in a helper column are the standard approaches since there is no Text to Columns wizard equivalent for in-place conversion.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Text number and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Why does my VLOOKUP return #N/A when looking up a number in a text-number column?
VLOOKUP uses exact type matching in approximate-match mode and strict value matching in exact-match mode. A numeric lookup value does not match a text '123', so #N/A is returned. Wrap the lookup value in TEXT() or convert the column to real numbers.
After I convert the cells, SUM still returns 0 — what else could be wrong?
If the cells have visible green triangles gone but SUM still returns 0, press Ctrl+Shift+F9 to force a full recalculation. Also check that the SUM range reference actually covers the converted cells.
How can I prevent numbers from being stored as text when importing data?
In Excel, use Data > Get Data (Power Query) and set the column data type to Whole Number or Decimal before loading. In Google Sheets, use IMPORTDATA or paste with Data > Split text to columns, then format the column as Number before pasting.