VALUE function

VALUE converts a text string that looks like a number, currency, or date into the underlying numeric value Excel or Sheets can use in calculations.

=VALUE(text)

Generate a VALUE formula

Describe what you need. The generator will reach for VALUE where VALUE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How VALUE reads its arguments
textrequiredVALUE →
ArgumentRequiredDescription
textRequiredA required string (or a reference to a cell containing a string) that Excel or Sheets will try to interpret as a number, date, or time; non-convertible strings cause a #VALUE! error.

Returns

It returns a single numeric scalar (a number) that can be used in further formulas.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Turn a price stored as text into a usable number

Order IDRegionRepUnitsUnit PriceOrder Date
1001NorthAlice5$15.992023-07-12
=VALUE(E2)

Result: 15.99

The Unit Price column contains a currency string with a leading dollar sign. VALUE strips the currency symbol and parses the remaining characters as a decimal number, returning the numeric value 15.99. This allows you to sum or multiply the price without first removing the $ sign manually.

2. Convert a text-based date to an Excel serial number

Order IDRegionRepUnitsUnit PriceOrder Date
1002SouthBob322.502023-07-15
=VALUE(F2)

Result: 45210

The Order Date column is stored as an ISO-style text string ("2023-07-15"). VALUE recognizes the pattern as a date, converts it to the internal serial number that represents July 15, 2023, and returns 45210. Once converted, you can perform date arithmetic such as subtracting dates or adding days.

3. Remove surrounding spaces and coerce a numeric string to a number

Order IDRegionRepUnitsUnit PriceOrder Date
1003EastCara 42 9.992023-07-18
=VALUE(D3)

Result: 42

The Units column contains the number 42 padded with leading and trailing spaces. VALUE automatically trims whitespace before conversion, so the function returns the clean numeric value 42. This is handy when data imports include extra spaces that would otherwise break arithmetic.

Common errors

Which VALUE error are you seeing?
VALUE returned an error#VALUE!
Ensure the cell contains a valid numeric representation or clean the string with SUBSTITUTE, LEFT, etc., before calling VALUE.
#VALUE!
Reference a single cell, or use an aggregation like SUM to combine the range before conversion.
#NUM!
Check the source data for overflow; if the value is meant to be a string, keep it as text or use scientific notation within Excel's range.
ErrorWhy it happensHow to fix it
#VALUE!The text argument cannot be interpreted as a number, date, or time (e.g., "ABC").Ensure the cell contains a valid numeric representation or clean the string with SUBSTITUTE, LEFT, etc., before calling VALUE.
#VALUE!You supplied a range (multiple cells) to VALUE, which expects a single string or number.Reference a single cell, or use an aggregation like SUM to combine the range before conversion.
#NUM!The text represents a number that is too large or too small for Excel's numeric limits (e.g., "1E309").Check the source data for overflow; if the value is meant to be a string, keep it as text or use scientific notation within Excel's range.

Tips and when to use something else

  • VALUE ignores leading/trailing spaces, so you rarely need TRIM before conversion.
  • If your text uses a different decimal separator (e.g., commas), prefer NUMBERVALUE, which lets you specify the separators.
  • When converting currency strings that include symbols other than the locale's default, use SUBSTITUTE to strip them first.
  • For pure date strings, DATEVALUE (not listed) is often clearer, but VALUE works equally well for standard date formats.

Related functions

TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.NUMBERVALUENUMBERVALUE converts a text string that represents a number—using any decimal or group separator—into a true numeric value that can be used in calculations.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.

Frequently asked questions

Why does VALUE return #VALUE! when I try to convert "12,34" in a European locale?
In many locales the comma is the decimal separator, but VALUE always expects the system's default. If your workbook uses a different separator, VALUE sees the comma as a thousands separator and fails. Use NUMBERVALUE instead, where you can specify "," as the decimal separator.
Can VALUE convert a time string like "14:30" to a serial time?
Yes. VALUE interprets time strings and returns the fractional day value (e.g., 0.6041667 for 14:30). After conversion you can format the cell as Time to see the familiar clock display.
What happens if the cell passed to VALUE is blank?
A blank cell is treated as zero, so VALUE returns 0. This can be useful for avoiding #VALUE! errors in calculations that include optional numeric inputs.
Is VALUE volatile? Does it recalculate on every sheet change?
VALUE is not a volatile function; it only recalculates when its argument changes. This means using VALUE in large worksheets has minimal performance impact compared with volatile functions like NOW or RAND.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator