Excel VALUE Formula Not Working? Fix Common Errors

If Excel’s VALUE formula is not working, first check whether the cell contains text Excel can interpret as a number and whether the text uses the decimal and thousands separators your Excel settings expect. For text in A2, enter =VALUE(A2) in another cell; if it returns an error or an unexpected result, clean hidden characters or use NUMBERVALUE with explicit separators.

What VALUE does—and what it does not do

VALUE converts a number represented as text into a numeric value Excel can use in calculations. For example, a cell might contain the characters “125” rather than the number 125. That difference can matter when you add, sort, or compare imported data. VALUE can also interpret text that represents a date or time, subject to the formats recognized by your regional settings.

With the text 125 in A2, enter =VALUE(A2) in B2; the result is the number 125. The displayed result may look identical to the original text, so check its behavior in a calculation or inspect the cell’s alignment and number format. A number formatted to look like text is still numeric; VALUE is mainly useful when the stored content is text.

VALUE does not repair every kind of malformed input. It cannot reliably guess what a string means when separators, currency symbols, or extra characters make the text ambiguous. It also does not change the source cell: the formula returns a converted result in the cell containing the formula. To replace the original text, you can copy the results and use Paste Special > Values.

Diagnose the error before changing the formula

Look at the exact cell contents, not just how they appear. Double-click the source cell or select it and inspect the formula bar. A leading apostrophe, imported spaces, a currency symbol, or a different decimal mark may explain why Excel treats the entry as text or cannot parse it. The green triangle warning for “Number stored as text” can be a clue, but not every text-to-number problem receives that warning.

#VALUE! usually means Excel could not interpret the text as a supported number, date, or time in the current context.
#NAME? can mean the function name was misspelled or entered in a way Excel does not recognize. Check that the formula begins with an equals sign and uses VALUE.
A result that looks like the formula may mean the destination cell is formatted as Text, the formula was entered with a leading apostrophe, or Show Formulas is on.
A result that is a different number often points to decimal and thousands separators being interpreted differently than intended.

Also confirm that the formula refers to the intended source cell. If A2 is blank, contains a label, or points to a different row than expected, fixing the formula’s format will not produce the intended value.

Check decimal and thousands separators

Separator conventions vary. In many English-language settings, a comma separates thousands and a period marks the decimal part: 1,234.56. Other regional settings use a period for thousands and a comma for decimals: 1.234,56. VALUE generally follows the conventions Excel recognizes on that computer. A string written for one convention may therefore be rejected or read differently under another.

NUMBERVALUE is useful when you know which separators the source text uses. It lets you specify the decimal separator first and the group separator second. With the text 1.234,56 in A2, where the comma is the decimal separator and the period groups thousands, enter =NUMBERVALUE(A2,",",".") in B2; the result is 1234.56 as a number. NUMBERVALUE is available in modern Excel, including Microsoft 365.

Do not remove punctuation blindly. If a comma is a decimal mark, deleting commas changes the value; “12,5” could become 125 instead of 12.5. Establish the source’s convention before converting a whole column. For a recurring import, make the separator choice part of the documented cleanup process.

Remove spaces and hidden characters

Imported data can contain spaces VALUE does not accept as part of a number. Ordinary leading or trailing spaces may be handled in some cases, but nonbreaking spaces and control characters can remain invisible in the grid. These characters often arrive when copying from websites, PDFs, or other systems. A cell can look like 125 while containing an extra character that prevents conversion.

With the suspect text in A2, enter =VALUE(TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))) in B2; if A2 contains 125 surrounded by ordinary spaces or nonbreaking spaces, the result is 125. CLEAN removes many nonprinting ASCII control characters, SUBSTITUTE changes the nonbreaking space represented by CHAR(160) into an ordinary space, and TRIM removes extra ordinary spaces at the ends and between words.

This cleanup formula is not a universal character scrubber. CLEAN does not remove every possible Unicode character, and unusual symbols may need to be identified and replaced separately. If the source includes a currency code or other text, decide whether that text is meaningful before removing it. Keep an untouched copy of imported data until you have checked the converted column.

Fix formulas displayed as text or not recalculating

If the cell displays =VALUE(A2) rather than a result, select it and check Home > Number Format. If the format is Text, change it to General, then re-enter the formula—press F2 and Enter, or delete and type it again. Merely changing the format may not cause an existing text entry to be parsed as a formula. Also check that there is no apostrophe before the equals sign.

If formulas appear throughout the worksheet instead of their results, toggle Show Formulas from the Formulas tab, or press Ctrl+` on Windows. The grave-accent key is commonly near the upper-left of the keyboard; the shortcut can vary with keyboard layout. On Mac, use Control+` where that shortcut is supported, or toggle Show Formulas from the Formulas tab.

If formulas show results that do not update after you change the source, check Formulas > Calculation Options and select Automatic if appropriate. In Manual calculation mode, Excel may wait for a recalculation command. Press F9 on Windows to recalculate; on Mac, use the calculation command available in Excel or its keyboard shortcut for your version. Calculation mode applies to the workbook environment, so consider the effect on other open workbooks before changing it.

Convert a whole column without a formula

For a one-time conversion, VALUE is not the only option. If Excel flags cells as numbers stored as text, select the affected cells and use the warning button’s Convert to Number option when it is available. This is convenient, but it may not appear for every imported format.

Another built-in route is Text to Columns. Select the text column, choose Data > Text to Columns, keep Delimited selected, and proceed through the wizard without selecting a delimiter that would split the data. Check the preview and choose the appropriate column data format if needed, then select Finish. Test on a copy first: this command writes converted results into the selected range and can affect neighboring data if the output would overwrite it.

Once converted, format the result as needed using Home > Number Format. Formatting controls how a number is displayed—such as with two decimal places—but does not itself turn text into a number. For repeated imports, a formula or a repeatable Power Query transformation may be more practical than manually converting the same column each time.

Choose the right fix for your data

The best method depends on why the conversion fails, not just on the error message. Use VALUE when the text already follows the conventions Excel expects. Use NUMBERVALUE when you know the source’s decimal and group separators. Add cleanup steps when invisible spaces or control characters are present. Use Convert to Number or Text to Columns for a direct, one-time correction when the data is consistent.

Simple digits stored as text: try VALUE or the Convert to Number warning option.
Text uses another region’s separators: use NUMBERVALUE and explicitly state those separators.
Copied or imported text has odd spacing: inspect it and clean likely spaces before conversion.
Dates or times: verify the intended date order and regional interpretation, then apply an appropriate date or time format.

When a formula returns a number that looks wrong, verify the meaning with a small known example before applying it to thousands of rows. For instance, confirm that “1,234” means one thousand two hundred thirty-four—not 1.234—under the source system’s conventions. A successful conversion only proves Excel produced a number; it does not prove Excel interpreted the source’s intent correctly.

Dates, currency, and display formats

VALUE may convert recognizable date and time text, but dates such as 03/04/2026 can mean different days in different regional conventions. Check the source format before trusting the result. Excel stores dates as serial numbers, so a converted date may initially display as a number. Apply a date format through Home > Number Format to make it readable; formatting changes the display, not the underlying value.

Currency symbols and thousands separators can also influence whether text is recognized. Do not assume a cell’s currency-looking display tells you what its stored content is. If a source mixes currencies, convert the numeric portion only after identifying the currency and preserving that information separately. VALUE converts text to a number; it does not perform currency exchange or determine what a monetary amount represents.

Frequently Asked Questions

Why does VALUE return #VALUE! in Excel?
Excel cannot interpret some part of the referenced text as a number, date, or time under the current settings. Check for hidden spaces, extra symbols, and mismatched decimal or thousands separators. If the source uses known alternate separators, try NUMBERVALUE with those separators specified.

Does changing Text format to General fix VALUE?
It can help when a formula was entered while the destination cell was formatted as Text, but changing the format alone may not re-enter the formula. After selecting General, re-enter the formula. Changing the source cell’s format also does not necessarily convert text already stored there.

Why does VALUE return a date serial number?
Excel stores dates as numbers. If the text was recognized as a date, the underlying result can be numeric while the cell displays a serial value. Apply a date format to the result cell, and confirm that Excel interpreted the date in the intended order.

Can VALUE convert numbers with commas?
It depends on what the commas mean and which conventions Excel recognizes. A comma may mark thousands or decimals. For predictable conversion of text with known separators, use NUMBERVALUE and specify the decimal separator and group separator in that order.

How do I replace the original text with the converted numbers?
Convert the values in a helper column, verify the results, then copy that column and use Paste Special > Values over the original cells if that is what you need. Keep a backup first, since replacing the source removes the original text.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.