=VALUE(A2) converts a number stored as text in A2 into a real number. Text numbers look normal, but SUM, AVERAGE and COUNT skip them, which is why a total can come out as 0:
| A | B | C | |
|---|---|---|---|
| 1 | Imported | VALUE | |
| 2 | 120 | 120 | |
| 3 | 85 | 85 | |
| 4 | 240 | 240 | |
| 5 | 15 | 15 | |
| 6 | 0 | 460 |
A6 totals 0 because the four cells in column A are text (typed with an apostrophe, as data imported from a CSV or a web page often is). Column C converts each one and C6 gives the real total, 460.
How to tell a number is stored as text
In Excel a number stored as text:
- sits at the left of the cell, where numbers sit at the right (unless the alignment was changed);
- shows a small green triangle in the cell's top-left corner, and a warning icon when you select it, saying "Number Stored as Text";
- is counted by COUNTA but not by COUNT.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ISNUMBER | ISTEXT | Text numbers | |
| 2 | 120 | TRUE | FALSE | 2 | |
| 3 | 85 | FALSE | TRUE | ||
| 4 | 240 | TRUE | FALSE | ||
| 5 | 15 | FALSE | TRUE |
E2 counts how many cells in the range are filled but not numbers: here the two typed with an apostrophe. On a clean column of numbers it returns 0.
Four formulas that convert
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | How |
| 2 | 1250 | 1250 | VALUE |
| 3 | 1250 | double minus | |
| 4 | 1250 | multiply by 1 | |
| 5 | 1250 | add 0 |
Any arithmetic forces Excel to read the text as a number, and VALUE is the explicit version. -- (two minus signs: minus, then minus again) is the common choice inside other formulas, because it is short: =SUMPRODUCT(--A2:A5) adds a column of text numbers without a helper column. VALUE also reads text with a currency sign, thousands separators or a percent sign: VALUE("$1,250") is 1250 and VALUE("12%") is 0.12.
| A | B | |
|---|---|---|
| 1 | Quantity (text) | Quantity |
| 2 | 125 |
Your turn: The quantity in A2 was imported as text. In B2, convert it to a number.
Text with units or other separators
VALUE returns #VALUE! when the text has anything it cannot read as a number. Two common cases:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Fixed | Without the fix |
| 2 | 120 kg | 120 | #VALUE! |
| 3 | 1.234,5 | 1234.5 | #VALUE! |
- A unit or a word: remove it first with SUBSTITUTE, as in B2.
- A comma as the decimal separator, as in
1.234,5from a German or Brazilian system: NUMBERVALUE (Excel 2013 and later) takes the decimal separator and the group separator as its second and third arguments. VALUE only knows the separators of your own Excel, so C3 fails.
Spaces before or after the digits do not stop VALUE, but non-breaking spaces from web pages can; SUBSTITUTE(A2,CHAR(160),"") removes them first. For the general causes of this error, see #VALUE!.
| A | B | |
|---|---|---|
| 1 | Amount | Total |
| 2 | 120 | |
| 3 | 45 | |
| 4 | 80 |
Your turn: The amounts in A2:A4 are numbers stored as text. In B2, return their total with one formula.
Convert in place without a formula
Formulas put the numbers in a new column. To fix the cells themselves:
- Convert to Number. Select the cells (the first selected cell must be one with the green triangle), click the warning icon next to the selection, and choose Convert to Number. This is the quickest fix.
- Text to Columns. Select the column, Data > Text to Columns, and click Finish straight away. Excel re-enters every cell and turns the text numbers into numbers.
- Paste Special, Multiply. Type 1 in an empty cell and copy it. Select the text numbers, Home > Paste > Paste Special, choose Multiply, click OK.
If the cells are formatted as Text (Home > Number Format shows "Text"), set them to General first; otherwise anything you type into them is stored as text again.
Common mistake: lookups between text and numbers
A lookup value of 101 does not match the text 101: VLOOKUP, XLOOKUP and MATCH return #N/A, and =A2=101 is FALSE, even though both cells look the same. Convert one side so both are the same type. When the lookup column is the text one, convert the value you look up instead:
=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE) E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE) E2 holds a text number, column A holds numbers
The ISNUMBER and ISTEXT page shows how to check each side.
Frequently Asked Questions
How do I convert text to a number in Excel?
With a formula, =VALUE(A2) or =--A2. Without a formula, select the cells, click the warning icon that appears next to them and choose Convert to Number.
Why does SUM return 0 in Excel?
The numbers are stored as text, and SUM skips text. They are usually aligned to the left and show a small green triangle. Convert them with =VALUE(A2), or add them directly with =SUMPRODUCT(--A2:A10).
Why does Convert to Number not work or not appear?
Excel offers it only for text it can read as a number. A non-breaking space from a web page, a unit such as kg or a decimal comma your Excel does not use hides the green triangle. Clean the text first: =VALUE(SUBSTITUTE(A2,CHAR(160),"")) or =NUMBERVALUE(A2,",",".").
How do I convert numbers with a comma as decimal separator?
Use NUMBERVALUE and name the separators: =NUMBERVALUE(A2,",",".") turns 1.234,5 into 1234.5. VALUE only understands the separators of your own Excel settings.
