How to convert numbers stored as text in Excel
Numbers imported as text look normal, but SUM skips them, and lookups don’t match them. They usually sit on the left of the cell, often with a small green triangle.
VALUE converts text that looks like a number into a real number.
The formula
| A | |
|---|---|
| 1 | Amount |
| 2 | 1250.5 |
=VALUE(A2)Result: 1,250.5
The text 1250.5 becomes the number 1250.5, which SUM and lookups can use.
Step by step
- In an empty column, type =VALUE(A2).
- Fill down.
- Paste the results over the original as values.
Other ways to write it
=A2*1Any arithmetic converts text to a number.=--A2A double minus does the same, and is common in other people’s formulas.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Changing the format
- Formatting a text cell as Number doesn’t convert what’s already in it.
- Currency symbols and spaces
- Text such as “$1,250 ” may need SUBSTITUTE or TRIM before VALUE can read it.
Reading isn’t the same as doing
Numbers and dates stored as text gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.