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
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
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)
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
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.
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
Example to Sum a Mixed Data Type Column in Google Sheets Query:
=Query({A2:A8,ArrayFormula(N(B2:B8))},"Select Sum (Col2)")
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 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:
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.
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.
Hi, Karim,
Thanks for your feeback!
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.
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)
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.)
Hi, Joe76,
Nope! It should be a semi-colon as it’s the header row.
You may like: How to Change a Non-Regional Google Sheets Formula.
Thank you !!! You saved me a lot of time explaining query mixed data problem in a complete article like this.