Google Sheets QUERY Not Displaying Cell Text with Numbers – Fix

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue.

Basically, I'm using a query to pull data from a Google Spreadsheet that is populated by a Google Form. If the form submits only numbers (100), there is no problem. However, if someone submits a string ($100 or 100 dollars), the query will not display the string. It will only display form submissions that are 100% numbers.

Also, if the 1st submission is a string (not 100% numbers) the query will work.

Here is my query

=query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, CI, BZ, CA, BA  where J = 'Industry Certification'",false)

Any ideas on why this is happening?

Also, I'm aware that I can limit the form to numbers only. I'm trying to avoid that and learn 'why' this is happening.

Best Answer

A good place to start is documentation:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

In other words, if you have a bunch of numbers and some text in the same column, the text will be ignored.

If you allow mixed data types within a column, query is not for you. You may be able to achieve the desired result with filter. The following example with filter is equivalent to the query in the question: 

=filter({data!B2:J, data!CI2:CI, data!BZ2:BZ, data!CA2:CA, data!BA2:BA},  data!J2:J = "Industry Certification")

The first argument is an array of data to be filtered (with { } used to combine non-adjacent columns). The second is an array of True/False values to be used as a filter. Multiple conditions can be used.

Related Topic