Learn the VALUE Function in Google Sheets and Advanced Uses

Published on

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.

Reasons to use the VALUE function in Google Sheets.

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"
Issue with QUERY for Aggregating a Mixed Data Type Column

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)
Advanced uses of the VALUE function in Google Sheets

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. Hi Prashanth,

    Is there a way to use the VALUE() function within the Query formula, or do you know of a possible alternative to get the string data in Col2 cast as numbers in the formula?

    • You need to use VALUE with the data part of the formula. Since you need to extract the second column and aggregate (no grouping involved), you can use the following formula:

      =ArrayFormula(AVERAGE(VALUE(TOCOL(CHOOSECOLS(range, 2), 3))))

      Replace “range” with your formula that imports values.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.