Fix Excel Numbers Stored as Text — From Green Triangles to European Decimals
If you see the Excel number stored as text warning and your total is 0, the numbers are stored as text. Use the Quick Fix below and the solutions by cause to resolve it all at once.
Quick Fix (3 Minutes)
- Select the range → click the error icon → Convert to Number.
- If there is no icon, use Data > Text to Columns (no delimiters → Finish), or use Paste Special: Multiply by 1 (Ctrl+Alt+V → Multiply).
Why Numbers Become Text
- Leading apostrophes (‘123), nonbreaking spaces (CHAR160), full-width numbers (１)
- European notation (1.234,56), or confusion between thousands and decimal separators
- Cells formatted as Text
7 Methods for Bulk Conversion
- Error checking: Convert to Number
- Text to Columns → Finish
- Paste Special: Multiply by 1
=VALUE(A2)(+TRIM/SUBSTITUTE)=NUMBERVALUE(A2, ",", ".")(European notation)- Replace leading apostrophes/full-width characters: Ctrl+H or
SUBSTITUTE - Power Query: Force the data type to Whole Number or Decimal Number
Values That Should Remain Text, Such as Dates and IDs
- Phone numbers, ZIP codes, card numbers, and product codes: keep as text
- Date text → date:
=DATEVALUE(A2)(+TIMEVALUE)
Practical Examples
// Remove NBSP, then convert to a number
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))
// European notation → Korean notation
=NUMBERVALUE(A3, ",", ".")
// Date
=DATEVALUE(A5)
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Total = 0 | Numbers stored as text | Text to Columns or Multiply by 1 |
| Not right-aligned | Text format | Set the cell format to General, then reevaluate |
| Changes to 123456 | 1.234,56 is interpreted incorrectly | NUMBERVALUE(A2,”,”,”.”) |
| Conversion fails because of spaces | CHAR160 | SUBSTITUTE(,CHAR(160),” “) → TRIM → VALUE |
| Leading zeros disappear | Identifier | Keep as text/use data validation |
Checklist to Prevent It from Happening Again
- Import using From Text/CSV and disable data type detection
- Set ZIP codes and IDs to Text
- For European notation, use NUMBERVALUE or a Power Query locale
- Turn on error checking and control input with data validation
Related Articles
- Preprocess text strings with TEXTSPLIT
- Fix garbled CSV files (UTF-8 and delimiters)
- Create an Excel drop-down list
- VLOOKUP multiple-criteria guide
- Practical SUMIFS and AVERAGEIFS patterns