HomeGoogle DocsSpreadsheetHow to Solve the Mixed Data Type Issue in Query in Google...

How to Solve the Mixed Data Type Issue in Query in Google Sheets

Published on

To solve or deal with the mixed data type issue in Query in Google Sheets, you can situationally use the functions To_Text, N, T, Datevalue and also the Info type functions.

The solution in Query to tackle the mixed data type issue is to format the concerned column using formulas or Format menu.

In this tutorial I am concentrating on the formula aspect, which is important to know.

To convert a column that contains mixed type data, use either of the above-mentioned functions. But such use solely depends on your data manipulation requirements.

Before getting into the detail, I think I must first clarify what is mixed data type in Query and how it affects its output.

Mixed Type Data: If any single column in your dataset contains text and numbers that column is a mixed type column. That makes the dataset mixed type.

The other combinations that makes a column mixed data types are;

Text and Date, Text and TRUE/FALSE Boolean Values, Numbers and Date and so on.

How a mixed type data column affects Query?

You will see missing values in the concerned column in Query. This is simply because, for query purposes, the majority data type determines the data type of the column. The minority data types are considered as null values.

Example to Mixed Data Type Column in Query in Google Sheets

In the following example, as you can see the column B contains mixed types of data.

=query(A1:B8,"Select A,B order by A Desc",1)
Example to Mixed Data Type Issue in Query in Google Sheets

Using Query I have just sorted that data order by column A in descending order. It causes some of the values in Column B (minority values which are string) missing in the output.

In the below examples, I am going to show you how to deal with mixed type data in Query in Google Sheets.

How to Deal With The Mixed Data Type Issue in Query in Google Sheets

The function To_text is widely popular among Sheets’ experts to tackle the mixed data type issue in Query. So let me begin with that function.

Handle The Mixed Data Issue Using To_text in Query Data

Here is the formula that uses To_Text in Query Data.

=ArrayFormula(query({A2:A8,to_text(B2:B8)},"Select Col1,Col2 order by Col1 Desc",0))

What does this formula do?

Here I have used a virtual two column data in Query instead of A1:B. To form that virtual column I have excluded the header row.

I have individually used the two columns of data. The second column in the dataset contains numbers and text and no doubt that makes it a mixed type data column.

The To_text function converts that numbers to text and make that column non-mixed.

To_Text function to solve mixed content column issue

The whole formula wrapped by the ArrayFormula since the function To_text is not an array formula by default. So in array use, we must enter it as an array formula.

Further, we can’t refer column Identifier as A and B. Instead use Col1 and Col2 in Query.

The above output does not contain the header row. Because we have omitted that row while forming the virtual data.

If you want, you can combine the header row outside the Query as below. It’s a common practice.

=ArrayFormula({A1:B1;query({A2:A8,to_text(B2:B8)},"Select Col1,Col2 order by Col1 Desc",0)})

I am not telling you that this is the perfect solution. In this, the numbers in column B converted to text. So when you use that column in calculations in Query, it may cause issues. Here comes the use of the function N in Query.

The Use of Function N in Query to Tackle Mixed Data Type Issue

Instead of the To_Text function, you can use the function N in Query. By doing so, you can use the Column B in calculations within Query.

The N retains the numbers as it is and for other values, I mean Text, it returns 0. In concise;

To deal with mixed data type issue in Google Sheets Query, you can use the function N in a limited way.

If the column contains text and numbers and you want to perform calculations in that column, use N. Don’t use N in a mixed type column that contains numbers and dates.

Example to Sum a Mixed Data Type Column in Google Sheets Query:

=Query({A2:A8,ArrayFormula(N(B2:B8))},"Select Sum (Col2)")
The use of the N formula in Query

How to Use The T Function in Query Data in Google Sheets

If you understand how to use the function N in Query, no need to spend extra time to learn the use of T in Query. N represents number and T texts.

If you give importance to the texts in a mixed type column, use the function T. It returns the text as it is and makes the numbers null.

=ArrayFormula(query({A2:A8,T(B2:B8)},"Select *"))
The use of the T formula in Query

The Use of Info Type Functions to Handle Mixed Data Type in Query

You can use ‘most of the’ info type functions with the logical IF in Query to deal with mixed data type issues. Here are a few example formulas.

Convert a Text and Number Column to Pure Number Column: For this purpose, I am using ISNUMBER in Query.

=ArrayFormula(query({A2:A8,if(isnumber(B2:B8),B2:B8,0)}))

This formula return the numbers and 0 for texts.

The same using ISTEXT.

=ArrayFormula(query({A2:A8,if(ISTEXT(B2:B8),0,B2:B8)}))

We can’t use the ISDATE, another info type function, similarly in an array.

As an example, if you want to handle a column with mixed date and text or date and number column, use the function DATEVALUE as below, not the ISDATE.

How to Use Datevalue Function in Query Data in Google Sheets

Formula:

=ArrayFormula(query({A2:A8,to_date(DATEVALUE(B2:B8))}))

Yes! Do use the To_date to convert back the date value to date.

Conclusion:

Why we create virtual columns as above?

We create virtual columns as above to surpass the Query behavior, i.e., the majority data type determines the data type of the column for Query purposes.

Some times we may want the minority data type. In that case, the mixed data type issue in Query in Google Sheets makes a cause of concern for us to worry. The above workarounds can come in handy at that time. That’s all. Enjoy!

Additional Resources:

  1. How to Insert Blank Columns in Google Sheets Query.
  2. Replace Blank Cells with 0 in Query Pivot in Google Sheets.
  3. Custom Sort Order in Google Sheets Query [Workaroud].
  4. How to Use Date Criteria in Query Function in Google Sheets.
  5. Simple Comparison Operators in Sheets Query.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

10 COMMENTS

  1. How will I place the curly bracket if it’s Query and Importrange?

    E.g:

    =query({IMPORTRANGE(C1,"Staff!A3:V");
    IMPORTRANGE(C1,"Staff_ATT!A3:V")},
    "select * where Col2 is not null",0)

    My column A is supposed to be all Numbers. However, whenever I consolidate them in one Sheet, it returns only blank.

    So I want to ensure that all datatype are either numbers or text.

    • Hi, Paulo,

      In that case, use FILTER.

      You may please try this one.

      =LET(data,VSTACK(IMPORTRANGE(C1,"Staff!A3:V"),
      IMPORTRANGE(C1,"Staff_ATT!A3:V")),
      filter(data,len(choosecols(data,2))))

      You can check my Function Guide to learn the new functions used above, such as LET, VSTACK, and CHOOSECOLS.

  2. Thank you very much for this article.

    I didn’t understand why some values were disappearing, and you explained it perfectly.

    I wanted to append the results of several QUERY functions and so wrapped the whole combination with another QUERY.

    One of the columns has a mixed type, i.e., boolean and text and the values were disappearing.

    What I did is I split the big long query into two parts (one for the booleans and one for the text).

    I didn’t use to_text at all.

  3. Works like wonder! Thanks, Prashant, would love to understand how this works if you could point me to the relevant post for an explanation, thanks again.

  4. Hi Prashant,

    I’ve been reading a number of your posts trying to figure out how to solve this, looks like I still need your advice. I need to extract a Date and Time from 2 columns and merge them into one column, at the same time still keeping them in Number format. After joining them I had a problem turning them back to Number again as I need it later to identify the month, date, etc.

    Attaching the “MD” sheet here for reference, cells in orange. Thank You, Prashant

    • Hi, Junnie Chen,

      Inserted the below formula in MD!G1.

      ={"Date";ArrayFormula(if(len('Form Responses 2'!T2:T),'Form Responses 2'!T2:T+'Form Responses 2'!U2:U,))}

      Column formatted to Date Time (Format > Number > Date Time)

  5. Your last formula in the TO_TEXT-part looks like this:

    =ArrayFormula({A1:B1;query({A2:A8,to_text(B2:B8)},"Select Col1,Col2 order by Col1 Desc",0)})

    I assume, that the written semicolon should be a comma for an English expression. (For German expressions we use semicolons in functions, outside of SELECT-statements.)

LEAVE A REPLY

Please enter your comment!
Please enter your name here