Excel VALUE Function Not Working? Fix Text-to-Number Errors

If Excel’s VALUE function returns #VALUE!, the text in the cell probably is not written in a number, date, or time format Excel recognizes under your regional settings. With the text to convert in A2, start with =VALUE(A2); if that fails, check for hidden characters, mismatched decimal or thousands separators, and text that is not actually numeric.

What the VALUE function does

VALUE converts text that represents a number into a numeric value Excel can calculate with. It can also interpret text that Excel recognizes as a date or time. For example, a cell containing the text 125 can be converted to the number 125. That matters when data comes from a website, a CSV export, a report, or another application that stores digits as text.

With the text 125 in A2, =VALUE(A2) returns the number 125. It may look identical to the original cell, but the result is a number rather than text. The distinction matters for arithmetic, sorting, charts, and many comparisons: text that looks like a number may not behave as a number.

VALUE does not guess what arbitrary text means. A label such as about 125, a product code such as AB125, or a string with unsupported punctuation is not a numeric value simply because it contains digits. In those cases, #VALUE! is an indication that Excel could not interpret the supplied text, not necessarily that the function itself is broken.

Check whether the source cell is text

First inspect the source rather than changing the formula repeatedly. A green triangle in a cell may indicate a number stored as text, but it is not displayed in every situation and is not a complete diagnostic. You can test the cell directly. With the imported value in A2, =ISTEXT(A2) returns TRUE if Excel sees text; with the same layout, =ISNUMBER(A2) returns TRUE if Excel already sees a number.

If ISNUMBER is TRUE, converting with VALUE is usually unnecessary. The cell could merely have a display format that makes the value look unusual. If ISTEXT is TRUE, examine the exact contents: click the cell and inspect the formula bar, or use the formula bar to look for spaces or symbols that are easy to miss. Also check whether the source includes a leading apostrophe; it forces an entry to be treated as text.

Do not assume that changing the cell’s number format fixes text. Selecting Home > Number Format and choosing Number changes how numeric values are displayed; it does not reliably turn text into a number. Conversion requires a formula or another conversion method.

Look for spaces and invisible characters

Ordinary spaces can appear before or after a value, and data copied from web pages or other systems can contain nonbreaking spaces. These characters may look like regular spaces but are not always removed by Excel’s usual trimming. Tabs and other control characters can also travel with imported text.

With a value such as 1 234.50 in A2, where the gap is a nonbreaking space and the decimal point matches your settings, use =VALUE(SUBSTITUTE(A2,CHAR(160),"")). This removes character 160 before VALUE converts the remaining text. The result is 1234.5 in a region that uses a period as the decimal separator.

For text with ordinary extra spaces and control characters as well as possible nonbreaking spaces, try a cleanup formula. With the imported string in A2, =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) replaces nonbreaking spaces with ordinary spaces, removes many nonprinting control characters, trims extra ordinary spaces, and then converts the result.

This cleanup is not a universal repair. CLEAN removes certain nonprinting characters, not every Unicode character that may arrive in copied data. If conversion still fails, compare the source with a value typed directly into a blank cell, or identify the unexpected character before removing it. Avoid deleting punctuation blindly: a comma or period might be a meaningful decimal or group separator.

Match decimal and thousands separators

Separator conventions vary. One file might write one thousand two hundred thirty-four and a half as 1,234.5; another might write it as 1.234,5. Excel interprets text according to its regional settings, so a string that is valid in the source system may not be valid in the current Excel environment. A pasted currency symbol or a different grouping convention can cause the same problem.

When you know the source convention, NUMBERVALUE lets you specify the separators explicitly. With European-style text 1.234,50 in A2, =NUMBERVALUE(A2,",",".") tells Excel that the decimal separator is a comma and the group separator is a period; the result is 1234.5. NUMBERVALUE is available in current Excel versions, including Microsoft 365.

Use explicit separators only when you know what the source text means. Replacing every comma with a period, for example, can corrupt data if commas are thousands separators or if the source mixes conventions. If the data has a consistent format, specify it; if it contains multiple formats, standardize or separate those records before converting.

Handle date and time text carefully

VALUE can convert text that Excel recognizes as a date or time, but interpretation depends on the written format and regional settings. A string such as 03/04/2025 is ambiguous: it can mean March 4 or April 3. VALUE cannot infer the intended convention from the characters alone. Confirm the source format before converting, especially when data combines records from different countries.

Excel stores dates as serial numbers and times as fractions of a day. After conversion, a date may appear as a number until you apply a date format; a time may display as a decimal until you apply a time format. That display does not by itself mean conversion failed. Format the result appropriately using the number format controls on the Home tab.

If dates arrive in a stable but nonstandard layout, changing the display format will not reinterpret the text. The safer approach is to parse the components according to the known source pattern or import the data with its date convention specified. Do not rely on VALUE for ambiguous dates when getting the day and month reversed would matter.

Understand the error and check the formula reference

#VALUE! usually means the supplied text could not be converted. But an error can also come from a formula that refers to the wrong cell, includes extra text, or passes along an error from another calculation. Check that the argument points to the intended source cell and that the source is not a heading, blank-looking formula result, or descriptive label.

A cell that appears blank may contain a formula returning an empty string, or it may contain spaces. Those are not the same as an actually empty cell. If the source formula produces text, VALUE may still be able to convert it when that text represents a recognized number; otherwise, it can return an error. Inspect the formula bar and, if needed, trace the source formula rather than assuming the visible appearance tells the whole story.

To show a readable message instead of an error while you investigate, use error handling. With the source value in A2, =IFERROR(VALUE(A2),"Check source text") returns the converted number when conversion succeeds and the message when it fails. This is useful for a report, but it does not fix the input. Keep the original data available so that a failed conversion is not mistaken for a valid result.

Choose the right conversion method

VALUE is a good choice when you want a formula-based conversion and the source strings follow a consistent format Excel recognizes. Choose a different method when you need to correct a specific import problem, preserve source values, or convert a large block without keeping helper formulas.

VALUE: useful for converting recognizable numeric, date, or time text one cell at a time or through a copied formula.
NUMBERVALUE: useful when the text’s decimal and group separators are known and differ from Excel’s current regional interpretation.
Text to Columns: useful for converting or parsing a selected imported column. In Excel for Microsoft 365 on Windows, find it on the Data tab. Review the preview and the column’s data format before completing the operation.
Power Query: useful for repeatable imports and larger data-cleaning steps. Set the column’s data type and, where needed, its locale in the query rather than relying on a conversion that may vary with a user’s regional settings.

For a one-time correction, a formula is easy to inspect and undo by removing the helper column. For a repeatable workflow, a controlled import or Power Query transformation can make the assumptions clearer. Whichever method you choose, compare several converted values with the original source, including values with decimals, negatives, and separators.

A reliable troubleshooting sequence

Work from the simplest checks toward more involved cleanup. This avoids masking a bad source value with an overly broad replacement. A practical sequence is:

Confirm the formula references the intended cell and that the source contains text representing a value.
Test whether Excel already sees the source as a number. If it does, investigate display formatting or the downstream formula instead of converting again.
Check the source’s decimal, group, date, or time convention and compare it with the current Excel settings.
Inspect for leading or trailing spaces, nonbreaking spaces, and control characters. Clean only characters you have identified or reasonably expect.
Try VALUE for standard text or NUMBERVALUE when the source separators are known.
Check the converted results against the original data before using them in totals, charts, or reports.

When the formula works for most rows but fails for a few, compare a failing row with a successful one character by character. A single currency symbol, a minus sign copied from formatted text, or a different space can explain why only some records fail. Preserve an untouched copy of imported data until the converted column has been validated.

Frequently Asked Questions

Why does VALUE return #VALUE! for a number that looks correct?
It may contain an invisible character, use separators that do not match your regional settings, or include extra text. Inspect the exact source contents and confirm the source’s number convention before applying cleanup.

Does changing the cell format from Text to Number convert the value?
Not by itself in every case. Number formatting controls the display of numeric values; text may remain text. Use VALUE, NUMBERVALUE, or an appropriate import conversion, then format the resulting number if needed.

Can VALUE convert dates?
Yes, when the date text is in a format Excel recognizes under the relevant regional settings. Ambiguous strings such as 03/04/2025 need a confirmed day-month convention; otherwise the converted date may not mean what the source intended.

What is the difference between VALUE and NUMBERVALUE?
Both convert numeric text, but NUMBERVALUE lets you state the decimal and group separators explicitly. That makes it useful for consistent data from a region whose conventions differ from the current Excel settings.

Why does the converted date or time show as a number?
Excel stores dates as serial numbers and times as fractions of a day. The conversion can succeed even if the result is displayed numerically; apply an appropriate date or time number format to show it in a familiar way.

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.