I’m stuck with a Google Sheets problem. I’ve got a spreadsheet from a student test form with over 160 questions. I’m trying to analyze the data by using a transpose and query function to move it to another tab. But there’s a snag.
My formula looks like this:
=query(transpose(query('Form Responses 1'!$A$2:$FF$2,"Select *",1)))
The issue is that when the data moves to the new tab, all the numeric scores turn into text. I think it’s because there are also string values in the data. I’ve tried to filter out the strings, but no luck so far.
Does anyone know a way to fix this? Or maybe a better approach to handle this kind of data? I’m out of ideas and could really use some help. Thanks!
I’ve dealt with this exact problem before, and it can be frustrating. One approach that worked for me was using a combination of QUERY and ARRAYFORMULA. Here’s what I did:
=ARRAYFORMULA(QUERY(TRANSPOSE(‘Form Responses 1’!A2:FF2), “SELECT * WHERE Col1 IS NOT NULL”, 0))
This formula transposes the data first, then applies the QUERY. The key is setting the header parameter to 0, which treats the first row as data, not headers. This often preserves the original data types.
If you still see some numbers as text, you might need to clean your source data. Check for any hidden spaces or formatting issues in the original spreadsheet. Sometimes, a quick trim() on the source data can work wonders.
Let me know if this helps or if you need more troubleshooting tips!
I’ve encountered this issue before, and it can be quite tricky. One solution that often works is to use the TO_NUMBER function within your QUERY. Try modifying your formula like this:
=QUERY(TRANSPOSE(QUERY(‘Form Responses 1’!$A$2:$FF$2, “SELECT TO_NUMBER(Col1), TO_NUMBER(Col2), … WHERE Col1 IS NOT NULL”, 1)))
Replace Col1, Col2, etc., with the actual column references. This approach forces numeric conversion for columns you know should be numbers. For text columns, omit the TO_NUMBER function.
If that doesn’t work, you might need to pre-process your data in the source sheet. Consider using a helper column with a formula like =IF(ISNUMBER(A2), A2*1, A2) to force number recognition before querying.
Remember, Google Sheets can be finicky with data types in complex formulas. Sometimes, breaking the process into steps can help isolate the issue.
hey oscar64, i’ve run into similar issues before. have u tried using the VALUE() function to convert the text back to numbers? something like:
=ARRAYFORMULA(IF(ISNUMBER(VALUE(your_query_result)), VALUE(your_query_result), your_query_result))
this should keep strings as is and convert numbers back. lemme know if it helps!