What it does
Imported lists often bring numbers in as text: a small green triangle appears in the corner of the cell, SUM skips those cells and VLOOKUP cannot match them with numbers. VALUE reads the text and turns it into a number Excel can calculate with.
The text is read according to the computer's regional settings. On an English system, "1,250.50" becomes 1250.5; on a system that uses a comma for decimals, such as Turkish or German, the same text gives an error. To read a foreign format regardless of your own settings, use NUMBERVALUE, which takes the separators as arguments: =NUMBERVALUE(A2,".",",").
If the text contains a currency, a unit or extra spaces, clean them first with SUBSTITUTE and TRIM. Two minus signs in front of the text (=--A2) do the same conversion; VALUE just says more clearly what it does. Text that looks like a date or a time is converted too, into its serial number.
Syntax
=VALUE(text)=SAYIYAÇEVİR(metin)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text, or cell, to convert. It must be written as a number, date or time in your regional format; spaces at the start or end are fine. A value that is already a number is returned as it is. |
Examples
1Convert amounts stored as text and add them up
Amounts exported from a web shop arrived as text, in whole units. Column B converts each one into a number, and E2 adds up the converted values.
=VALUE(A2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Amount (text) | Amount | Total | ||
| 2 | 1250 | 1,250.00 | 14,705.00 | ||
| 3 | 980 | 980.00 | |||
| 4 | 12400 | 12,400.00 | |||
| 5 | 75 | 75.00 |
- B2 (filled down to B5)
=VALUE(A2) - E2
=SUM(B2:B5)
Column B now holds the numbers 1,250.00, 980.00, 12,400.00 and 75.00, and their total is 14,705.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Turn a quantity with a unit into a number and price it
The quantity column has "pcs" written next to each number. SUBSTITUTE removes the unit, VALUE turns the rest into a number, and the result is multiplied by the unit price.
=VALUE(SUBSTITUTE(B2," pcs",""))*C2| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Quantity | Unit Price | Amount |
| 2 | Grounded socket | 120 pcs | 92.50 | 11,100.00 |
| 3 | Circuit breaker | 45 pcs | 132.00 | 5,940.00 |
| 4 | Cable duct | 8 pcs | 68.00 | 544.00 |
| 5 | Light switch | 200 pcs | 41.75 | 8,350.00 |
- D2 (filled down to D5)
=VALUE(SUBSTITUTE(B2," pcs",""))*C2
D2 shows 11,100.00 and D5 shows 8,350.00. Without removing the unit, VALUE would return #VALUE!.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Match a code stored as text against a list of numbers
The product numbers in the order list are text; in the price list they are numbers. Excel does not match the text "1525" with the number 1525, so VALUE converts the lookup value first.
=VLOOKUP(VALUE(A2),$F$2:$G$5,2,FALSE)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product No | Qty | Unit Price | Product No | Price | ||
| 2 | 1525 | 120 | 52.00 | 1525 | 52.00 | ||
| 3 | 210 | 25 | 92.50 | 210 | 92.50 | ||
| 4 | 16 | 10 | 132.00 | 16 | 132.00 | ||
| 5 | 2040 | 40 | 68.00 | 2040 | 68.00 |
- C2 (filled down to C5)
=VLOOKUP(VALUE(A2),$F$2:$G$5,2,FALSE)
All four rows match: 52.00, 92.50, 132.00, 68.00. Without VALUE, the same formula would return #N/A on every row.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The text cannot be read as a number: it contains letters, a currency code or separators from another region (such as "1.250,50" on an English system).
Fix: Remove the extras with SUBSTITUTE; for a foreign format, use NUMBERVALUE.
- Wrong magnitude
Cause: The separators were read differently from what was meant: "1.250" is one point two five on an English system but one thousand two hundred and fifty on a Turkish one.
Fix: If you know the format of the source, use NUMBERVALUE and state the separators.
Good to know
- To convert a whole column without formulas, select it, open Data > Text to Columns and click Finish straight away.
- To see which cells hold text, add
=ISTEXT(A2)next to them; the ones that return TRUE are waiting to be converted.
In Nexell
Get Nexell to do it from a sentence
Convert the amounts that came in as text into numbers and add a total below them
Convert to tableBring your table into the tool and type this. Nexell turns the amounts that came in as text into numbers, adds a total below and shows it in a preview first; the total stays a formula in the file.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I convert numbers stored as text to numbers in Excel?
Type =VALUE(A2) next to them and fill down, then copy the results and paste them back with Paste Values. Without formulas: select the cells, click the warning icon that appears and choose Convert to Number.
What is the difference between VALUE and NUMBERVALUE?
VALUE reads the text with your computer's regional settings. NUMBERVALUE lets you state the decimal and group separators, so it is safer for files from other countries.
Related functions
- TEXTMETNEÇEVİR · Formats a number or date as text ("dd/mm/yyyy", "#,##0.00", "0%", "mmmm", "dddd").
- NUMBERVALUESAYIDEĞERİ · Converts text to a number with given separators.
- SUBSTITUTEYERİNEKOY · Substitutes new text for old text.
- TRIMKIRP · Removes extra spaces.
- ISNUMBERESAYIYSA · TRUE if the value is a number.