VALUEformula 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
- Examples of using
VALUEformula not working?
- Similar formulas to
VALUE formula with the syntax shown below, it has 1 required parameter:
- 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.
ExamplesHere are a few example use cases that explain how to use the
VALUEformula 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.
VALUEnot working? Here are some common mistakes people make when using the
VALUEGoogle 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:
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
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.
MAXfunction 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.
MINformula 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.
You can learn more about the
VALUE Google Sheets function on Google Support.