Google Sheets Query to Filter Numbers Only From Mixed Data Column

0
153

Here I am with a very useful Google Sheets Query function tutorial. Do you know how to use Google Sheets Query to filter numbers only from a column that contains mixed data type? With Filter function it’s easy to do. But Query is more capable in analysing data so you should learn it.

Quite recently, I was trying to apply a percentage calculation using Query and count aggregation function in it. But sadly the count in Query counts both texts and numbers. Further as you may know, Google Sheets Query function behaves unpredictably with mixed data types in a single column as it only considers the majority of data types and the rest of the data it may consider as null values. How to overcome this.

How to Use Google Sheets Query to Filter Numbers Only from Mixed Type Data in a Column

First let us see the said formula and example. Then I will tell you what may happen if we apply the count aggregation function in it.

Formula:

=QUERY(A3:A,”select A where A matches ‘[0-9\-.]+’ “,0)
Note: Re type all the single as well as double quotes if you copy from here.

With this example, you can learn how to use Google Sheets Query to filter numbers only. Here I’ve used ‘matches’ regular expression in “where” clause. You can follow this example in your own sheet.

But keep in mind that the values returned by the above formula are now in text format because of the use regular expression. So how to convert this numbers in text format to numeric values in Google Sheets Query? See that formula.

=ArrayFormula(value(QUERY(A3:A,”select A where A matches ‘[0-9\-.]+’ “,0)))

What I’ve done here is, I’ve just used Value function and ArrayFormula to convert the numbers in text format to numeric values. Hope you have learned how to use Google Sheets Query to filter numbers only, when a column has mixed data type.

Now see two Query formulas below. Both of them count column A that with and without filtering out text values.

The above two example formulas shows how to use Query in a mixed data type column to get the desired result.

The formula with red boarder counts all the values in column A. But the answer may or may not be correct as Query some times skips values in such mixed data type columns. But the second formula in blue boarder filters the column for numeric values and count properly.

That’s all for now. Enjoy!