VALUE Formula Not Working in Excel? Causes and Fixes

If Excel’s VALUE formula is returning an error or leaving text unchanged, check whether the cell contains a number written in a format your Excel settings recognize, and look for hidden characters or a formula stored as text. VALUE converts recognizable numeric text into a number; it cannot interpret every string, and regional settings can affect what it accepts.

What the VALUE formula does

Excel’s VALUE function converts text that represents a number into a numeric value. That distinction matters because a cell can look like a number while Excel treats its contents as text. Text-formatted numbers may not calculate as expected in arithmetic, sorting, charts, or other formulas. VALUE can turn suitable text into a number that Excel can use in those operations.

With the text value 125.50 in A2 and a regional number format that uses a period for decimals, enter this formula in B2:

With the text 125.50 in A2, =VALUE(A2) returns the number 125.5. The displayed result may show fewer decimal places if B2’s number format hides trailing zeros.

VALUE can also recognize some text representations of percentages, dates, and times, depending on the text and regional settings. It does not simply remove unwanted letters or guess what a string means. For instance, text such as about 25 is not a numeric value just because it contains digits. If the content does not match a format Excel can interpret, VALUE returns #VALUE!.

Check the error and the source cell first

Before changing the formula, inspect the original cell. Select it and look in the formula bar to see the actual contents, including leading apostrophes or unexpected spaces. Also check whether the source cell is genuinely text: a value that looks numeric may already be a number, in which case converting it is unnecessary.

With the source value in A2, enter this diagnostic formula in B2:

With A2 containing text such as 125.50, =ISNUMBER(A2) returns FALSE; with a numeric value in A2, it returns TRUE. This tests the cell’s underlying type rather than its appearance.

Note the exact error before troubleshooting. #VALUE! usually means Excel could not convert the supplied content. #NAME? can indicate a misspelled function or a formula entered as text. A visible formula instead of a result may point to cell formatting or calculation settings, not a conversion problem. Those issues need different fixes.

Fix decimal and thousands separator mismatches

Regional settings determine how Excel reads decimal and thousands separators. In one setting, 1,234.56 means one thousand two hundred thirty-four and 56 hundredths. In another, the comma may be the decimal separator and the period may mark thousands. If text was copied from a website, exported from software, or created in another country’s format, its separators may not match the format Excel expects.

With the text 1,234.56 in A2 and Excel configured to recognize a comma for thousands and a period for decimals, =VALUE(A2) returns 1234.56. If your Excel settings expect different separators, the same text may produce #VALUE! or be interpreted differently. Do not remove punctuation until you have confirmed what each mark means: stripping a decimal separator can change the amount substantially.

Compare a failing cell with a value you can enter directly in the worksheet and check the decimal and thousands separators used by the source system. If you control the source data, the safest long-term fix is often to export it in a format that matches the regional settings used by the people opening the workbook. Excel’s separators can be affected by Windows regional settings and Excel’s own options; changing them may affect more than one workbook, so check the consequences before doing so.

Dates deserve special care. A text date such as 03/04/2026 can be read as March 4 or April 3 depending on the date convention. VALUE may convert a date-like string to an Excel date serial, but a successful conversion does not prove Excel interpreted the date as intended. Verify the result by applying a date format and comparing it with the source convention.

Remove hidden spaces and nonprinting characters

Copied data may contain ordinary spaces, nonbreaking spaces, or other characters that are difficult to see. These can prevent conversion even when the visible text looks like a number. TRIM removes ordinary extra spaces, but it does not remove every invisible character. A nonbreaking space, often found in copied web content, may need to be replaced explicitly.

With A2 containing digits separated by a nonbreaking space, such as 1 234, enter the following in B2:

With the nonbreaking space between the digits in A2, =VALUE(SUBSTITUTE(A2,CHAR(160),"")) removes that character and returns 1234, assuming the remaining text is a valid number in your regional format.

For ordinary spaces around a value, TRIM may be enough. If A2 contains a number with spaces around it, =VALUE(TRIM(A2)) returns the converted number when the remaining text is valid. If the number itself includes grouping spaces, removing them may also be appropriate, but only when those spaces are known to be grouping marks rather than meaningful content.

Do not use broad cleanup formulas blindly on a whole column. First inspect representative source values, including the longest and most unusual entries. A formula that removes a character safely from one data source might corrupt another—for example, removing a separator that represents a decimal point.

Make sure the formula is entered as a formula

If the cell displays =VALUE(A2) literally rather than showing a result, Excel may be treating the entry as text. Select the formula cell and set its number format to General using Home > Number Format, then edit the cell and confirm the formula again. On Windows, F2 edits the selected cell and Enter confirms it. On a Mac, F2 may require the fn key depending on the keyboard settings; double-clicking the cell is another way to edit it.

A leading apostrophe can also make an entry text. Check the formula bar for an apostrophe before the equals sign, remove it if present, and confirm the formula. Another possibility is that Show Formulas is enabled, which displays formulas across the sheet instead of their results. Toggle it with Ctrl+` on Windows; on a Mac, the shortcut can depend on the keyboard layout, so use Formulas > Show Formulas if available in the ribbon.

If a formula appears correct but its result does not update after the source changes, check Formulas > Calculation Options > Automatic. In manual calculation mode, Excel may not recalculate immediately. Press F9 to recalculate formulas; on a Mac, use Fn+F9 if the function keys control system features. Calculation mode can affect the workbook, so confirm that automatic calculation is appropriate for your file.

Check whether VALUE is the right tool

VALUE is useful when a cell contains one complete numeric string, such as 125.50. It is not a general-purpose text-cleaning or extraction function. If the cell contains a currency code, a product label, or several numbers mixed with words, first decide which part should become numeric and handle the extra characters deliberately.

For example, a string like USD 125.50 may not be accepted by VALUE, depending on the exact text and regional settings. Removing the currency label may be reasonable if the column is known to contain amounts in one currency, but it is unsafe if different currencies are mixed. Keep the currency information in a separate field when it matters.

Also distinguish between a number formatted as text and a number with a custom display format. A numeric cell may show a currency symbol or thousands separators because of its number format, even though the underlying value is already numeric. Re-converting it is unnecessary. ISNUMBER provides a quick check, while the formula bar and number format help explain what the cell actually contains.

A reliable troubleshooting sequence

Use a small test before applying a repair across hundreds of rows. The goal is to identify whether the problem is the formula, the source text, the regional format, or the workbook’s calculation settings.

Click the source cell and inspect its contents in the formula bar. Look for apostrophes, spaces, currency labels, and punctuation.
Use ISNUMBER to determine whether the source is already numeric. If it is, investigate formatting or downstream formulas rather than converting it again.
Test VALUE on one simple example whose meaning is certain. Confirm that the separators match your regional settings.
If the source includes hidden spaces, clean only the specific characters you have identified, then test the result on a few representative rows.
If the formula is displayed instead of calculated, check the cell format, Show Formulas, and calculation mode.
Compare the converted output with the source before replacing or deleting original data. Keep a copy of the raw column until the results are verified.

For a temporary diagnostic, IFERROR can replace an error with a message. With the value to convert in A2, =IFERROR(VALUE(A2),"Check source") displays the number if conversion succeeds and Check source if it fails. That message can make a worksheet easier to scan, but it does not fix the underlying text. During investigation, retaining the error or using a clear diagnostic label is usually more informative than hiding the failure with a blank result.

When to use another approach

If you repeatedly receive files with inconsistent formats, cleaning the source or setting up an import process can be more dependable than adding a VALUE formula to every workbook. For a one-time conversion, Excel may also offer a warning icon for numbers stored as text, with a Convert to Number option. The warning appears only in some situations; its absence does not prove every cell is numeric.

For larger repeatable imports, Power Query can help standardize data types and regional interpretation. In Excel for Microsoft 365 on Windows, open the data in Power Query, select the relevant column, and use Transform > Data Type > Using Locale when the source uses a known locale. Check the preview and resulting values before loading the data back into the worksheet. The Power Query interface and available options can vary by platform and update; Excel for the web does not provide the same full desktop editing experience.

Choose the approach that fits the cause. VALUE is direct for clean numeric text. A targeted SUBSTITUTE or TRIM can handle known unwanted characters. A corrected export or locale-aware import is preferable when the underlying source convention is inconsistent. None of these methods can safely infer the meaning of ambiguous text without a clear rule.

Frequently Asked Questions

Why does VALUE return #VALUE! in Excel?
Excel cannot interpret the supplied text as a number using the current format and regional settings. Check for incompatible separators, hidden characters, labels, or mixed text, then test a cleaned example before changing the full dataset.

Can VALUE convert a number stored as text?
Yes. If the cell contains text that represents a number in a format Excel recognizes, VALUE returns a numeric result. If the cell is already numeric, conversion is generally unnecessary; check with ISNUMBER.

Why is Excel showing my VALUE formula instead of the result?
The formula cell may be formatted as Text, the entry may have a leading apostrophe, or Show Formulas may be turned on. Set the cell to General, confirm the formula again, and check the Show Formulas setting.

Does VALUE work with dates and percentages?
It can convert recognizable date, time, and percentage text, but interpretation depends on the text and regional settings. For dates with ambiguous day and month order, verify the resulting date rather than assuming the conversion is correct.

Should I use VALUE or convert the column to numbers?
For a single formula-driven conversion, VALUE is useful. For a one-off column, Excel’s Convert to Number option may be quicker when available. For recurring imports with known regional differences, a repeatable import cleanup is usually easier to audit.

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.