How to Fix Numbers Stored as Text in Excel
Convert numbers stored as text in Excel with the warning menu, VALUE, Text to Columns, or Paste Special without losing codes or precision.
First decide whether the value should be a number
A value that looks numeric is not always a quantity. Postal codes, account IDs, SKUs, phone numbers, and long identifiers should often remain text because arithmetic is meaningless and leading zeros matter. Excel keeps only 15 significant digits, so converting a longer identifier permanently alters its trailing digits.
Symptoms of unwanted numeric text include left alignment under General format, a green triangle in the cell corner, totals that ignore cells, and sorts such as 1, 10, 2. Confirm the column’s meaning before a bulk conversion, and make a copy when the import is important.
Method 1: use the warning menu
In Excel for Windows and Mac, flagged cells show a small green triangle. Select the affected range, click the warning icon that appears beside it, and choose Convert to Number. The values become numeric and the warning disappears. Apply the desired number format afterward. If the indicator is missing, for example in Excel for the web, or the warning option was switched off, use VALUE or Paste Special instead.
Changing the Number Format dropdown alone does not change the stored type: a text value can stay text under a numeric-looking format. Verify with =ISNUMBER(A2), a SUM, or the status bar rather than alignment.
Method 2: VALUE, Text to Columns, or Paste Special
=VALUE(A2) converts recognizable numeric text in a helper column. It is transparent and repeatable, and NUMBERVALUE is useful when decimal and group separators must be specified. After validation, copy the helper results and use Home > Paste > Values over a safe destination.
In desktop Excel, selecting the column and choosing Data > Text to Columns > Finish can force a reinterpretation of the values; check the preview first. Another established method is to copy an empty cell, select the text numbers, then use Paste Special > Add, or copy a cell containing 1 and use Paste Special > Multiply. Both change the source cells, so reserve them for a backed-up, confirmed range.
Clean characters before conversion
VALUE returns #VALUE! for unrecognised labels such as USD, for nonbreaking spaces, and for unfamiliar separators. Currency symbols that match your regional settings, such as $ in a US locale, usually convert. Use TRIM and CLEAN for ordinary imported padding, SUBSTITUTE for a known label, or NUMBERVALUE when separators are explicit. Do not remove characters you do not understand: a minus sign, decimal separator, or percent sign changes the meaning.
After converting, compare the count of numeric cells with the source, then check SUM, MIN, and MAX against an independent total. If a supplier switches from 1,234.50 to 1.234,50, an explicit NUMBERVALUE rule makes the change visible instead of quietly corrupting results.
Your turn
In B2, convert the text amount in A2 to a number.
| A | B | C | |
|---|---|---|---|
| 1 | Imported text | Number | |
| 2 | 125 | ||
| 3 | 80.5 | ||
| 4 | 42 |
Answer: =VALUE(A2)
Worked example
Three amounts imported as text (typed with a leading apostrophe) and one with a label. SUM skips text, so the total shows nothing until the cells are converted.
| A | |
|---|---|
| 1 | Imported |
| 2 | 125 |
| 3 | 80.5 |
| 4 | 42 |
| 5 | 80.5 USD |
| Formula | Result |
|---|---|
=ISTEXT(A2) | TRUE |
=SUM(A2:A4) | 0 |
=VALUE(A2)+VALUE(A3)+VALUE(A4) | 247.5 |
=SUMPRODUCT(--A2:A4) | 247.5 |
=A2*1 | 125 |
=VALUE(A5) | #VALUE! |
=VALUE(SUBSTITUTE(A5," USD","")) | 80.5 |
=NUMBERVALUE("1.234,50",",",".") | 1234.5 |
Quick mistake checklist
- Converting IDs, phone numbers, or long codes that should remain text.
- Changing only the display format and assuming storage changed.
- Removing separators or signs without validating their meaning.
Frequently asked questions
Why does SUM ignore some values?
Those cells may contain numeric text. Convert confirmed quantities to numbers and recheck the total.
Will Number format convert text to a number?
Not reliably. Use Convert to Number, VALUE, Text to Columns, or another actual conversion method.
How do I preserve leading zeros?
Keep identifiers as text or use an appropriate custom display format for true numeric values.
Why does VALUE return #VALUE!?
The text may include unsupported characters, labels, or separators. Inspect and clean the source deliberately.
Practice the skill
Continue with a checked FormulaCamp lesson: The TEXT and VALUE Functions
Open the lesson