How to use VALUE Google Sheets formula?

The syntax for the VALUE formula is =VALUE(text), it has 1 required parameter.

Why is VALUE Google Sheets formula not working?

One common reason the VALUE formula may not be working is due to using non-numeric text as input. If you use a non-numeric text string as input to <code>VALUE</code>, it will return the #VALUE! error. Make sure that the input text is in a format that can be converted to a number, date, or time value.

VALUE

The VALUE formula in Google Sheets converts a text representation of a number to an actual number. This formula is most commonly used when you have imported data from another source that is formatted as text, but you need to use it in a numerical calculation.

How to use VALUE formula?
Examples of using VALUE formula
VALUE formula not working?
Similar formulas to VALUE

Usage

Use the VALUE formula with the syntax shown below, it has 1 required parameter:

=VALUE(text)

Parameters:

text (required):
The text string that represents a number you want to convert to a numerical value. This parameter is required and must be enclosed in quotation marks.

Examples

Here are a few example use cases that explain how to use the VALUE formula in Google Sheets.

Converting imported data

If you import data from another source, it may be formatted as text. Using VALUE can convert this text data to numerical data, allowing you to perform calculations on it.

Extracting numbers from text

If you have a string of text that contains a number, you can use VALUE to extract just the numerical value from the text.

Comparing numerical values

When comparing numerical values, it is important to ensure that they are both in numerical format. VALUE can convert any text representations of numbers to numerical format for easy comparison.

Common Mistakes

VALUE not working? Here are some common mistakes people make when using the VALUE Google Sheets Formula:

Using non-numeric text as input

If you use a non-numeric text string as input to VALUE, it will return the #VALUE! error. Make sure that the input text is in a format that can be converted to a number, date, or time value.

Using text in an unrecognized format

If you use a text string that is not recognized as a valid number, date, or time format, VALUE will return the #VALUE! error. Make sure that the input text is in a recognized format.

Using a cell reference that contains text

If you use a cell reference that contains text as input to VALUE, it will return the #VALUE! error. Make sure that the cell reference contains a valid number, date, or time value.

The following functions are similar to VALUE or are often used with it in a formula:

SUM
The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
AVERAGE
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
MAX
The MAX function returns the maximum value from a range of cells or a set of supplied values. It is commonly used to find the largest value in a dataset.
MIN
The MIN formula is a statistical function that returns the smallest value in a set of numbers. It is commonly used to find the minimum value from a range of cells or a list of values.

Learn More

You can learn more about the VALUE Google Sheets function on Google Support.