Unlike most functions in Google Sheets, you can easily learn the VALUE function without needing an example. It’s that simple. So, I’ll jump straight into the purpose and syntax of the VALUE function. Don’t worry, I’ve included plenty of examples to illustrate both basic and advanced uses of this function.

Purpose of the VALUE Function in Google Sheets

The purpose of the VALUE function is to convert a text string representing a number, date, or time into a numeric value, making it usable in calculations.

You may encounter numbers, dates, or times stored as text in three main ways:

1. They were entered in a column formatted as text (Format > Number > Plain Text).
2. They are imported from external sources.
3. They result from text functions such as REGEXREPLACE or REGEXEXTRACT.

These text-formatted values lose their numeric properties and cannot be used in mathematical operations. The VALUE function in Google Sheets addresses this by converting them into a usable numeric format.

Syntax

VALUE(text)

Where text is the string, cell reference, or expression containing the value to be converted. If the text argument is an array or range, you should use the ARRAYFORMULA function to handle multiple values at once.

How to Use the VALUE Formula in Google Sheets

Let’s explore a few basic examples of how to use the VALUE function.

Example 1: Using the VALUE Function to Convert Text-Formatted Numbers

Suppose we have numbers in the range A1:A5, but they are formatted as text, which you can tell because they are left-aligned.

If you use the following SUM formula in cell A6, it returns 0 because the formula cannot recognize the numbers as numeric values:

=SUM(A1:A5)

To convert a text-formatted number back to a numeric value in an individual cell, you can use the following formula:

=VALUE(A1)

However, since we are working with a range (A1:A5), we need to use the ARRAYFORMULA function, as mentioned in the syntax section above:

=ARRAYFORMULA(VALUE(A1:A5))

This formula converts the entire range of text-formatted numbers back to numeric values. Now, you can use the SUM formula successfully:

=ARRAYFORMULA(SUM(VALUE(A1:A5)))

Example 2: Using the VALUE Function to Convert Text-Formatted Dates

Here’s how you can convert a date stored as text into a numeric value that represents a date:

=TO_DATE(VALUE(A1))

In this case, you need to use the TO_DATE function along with the VALUE function. Without TO_DATE, the VALUE function will return the raw date serial number, which is sufficient for calculations but not ideal for readability.

To apply this to a range of text-formatted dates, use the ARRAYFORMULA:

=ARRAYFORMULA(TO_DATE(VALUE(A1:A5)))

Example 3: Using the VALUE Function to Convert Text-Formatted Time

Let’s see how the VALUE function works with time. If you have a time formatted as text, such as “10:10:45” in cell A1, the VALUE function will convert it to a numeric value like: 0.4241319444.

This numeric value represents the fraction of the day, but it doesn’t retain the time format. To preserve the time format, you can use the QUERY function with a formatting clause:

=QUERY(VALUE(A1), "FORMAT Col1 'HH:MM:SS'", 0)

This formula retains the time format “HH:MM:SS”. If you need to apply it to a range, use the ARRAYFORMULA:

=ARRAYFORMULA(QUERY(VALUE(A1:A5), "FORMAT Col1 'HH:MM:SS'", 0))

This approach allows you to handle time values formatted as text and ensures they remain in the correct time format after conversion.

Advanced Use of the VALUE Function in Google Sheets

You can use the VALUE function in complex formulas involving QUERY, VLOOKUP, and other functions.

When you use aggregation functions like SUM or AVG in a QUERY on a column containing both text and numbers, you may encounter an error message like:

"Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC"

In this situation, you can indirectly utilize the VALUE function. While I won’t go into complete detail, I will explain how to filter only the numeric values from a column using QUERY.

The following formula will return only numbers from column E, but they will still be formatted as text due to the regular expression used:

=QUERY(A1:E7, "SELECT E WHERE E MATCHES '[0-9-.]+'", 0)

For more details about this formula, follow the link below:

Must Read: How to Filter Numbers Only From a Mixed Data Column

As mentioned at the beginning of this tutorial, we should use the ARRAYFORMULA when applying the VALUE function to an array. You can use the following formula to convert the text-formatted numbers returned by the QUERY back to numeric values:

=ARRAYFORMULA(VALUE(QUERY(A1:E7, "SELECT E WHERE E MATCHES '[0-9-.]+'", 0)))

This formula will return the values after filtering out the text in column E.

Now, you can run the following QUERY to return the first four columns (A, B, C, and D):

=QUERY(A1:E7, "SELECT A, B, C, D WHERE E MATCHES '[0-9-.]+'", 0)

You can combine these two queries using curly brackets and use them in calculations as follows:

=QUERY({QUERY(A1:E7, "SELECT A, B, C, D WHERE E MATCHES '[0-9-.]+'", 0), ARRAYFORMULA(VALUE(QUERY(A1:E7, "SELECT E WHERE E MATCHES '[0-9-.]+'", 0)))}, "SELECT Col1, SUM(Col5) GROUP BY Col1", 0)

This approach allows you to filter and manipulate data effectively, enabling calculations with mixed data types.

Conclusion

To master Google Sheets, it’s essential to learn as many functions as possible, regardless of their complexity. Even simple functions can enhance your data analysis capabilities when you combine them with complex formulas. Be sure to check out my function guide for more insights.