Google Sheets QUERY won't display cell text if other cells have numbers

Question

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.

Answer 1

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.

Answer 2

• combo of TO_TEXT, ARRAYFORMULA and Col references should solve it:

=ARRAYFORMULA(QUERY(TO_TEXT(data!B2:CL),
 "select Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col87, Col78, Col79, Col53 
  where Col10 = 'Industry Certification'", 0))

Answer 3

In the 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.

I solved the problem like this. If my data is in the range "A1:E100" my query is going to be:

ArrayFormula(TRIM(UPPER(QUERY(A1:E100;"select *"))))

With this formula, if any column contains multiple data types the problem will continue so we have to try something like this:

=QUERY(ArrayFormula(TRIM(UPPER({A1:E100})));"SELECT *")