Query formula ignores string values

Question

I have a table that is a result of a few smaller tables merged together. It's a result of searching a few sheets for rows that meet filtering criteria.

I wanted to remove empty rows using QUERY formula but it works in a strange way!

Normally

=QUERY(A1:Z,"Select *",0)

should return a full table. But not in this case.

What I actually try to do is to remove empty rows. I tried:

 =QUERY({A1:Z},"Select * where Col5 is not null",0)

as column E is empty only when whole row is empty. But it does not work. It seems to ignore string values and sees only numbers.

Here is dummy table. https://docs.google.com/spreadsheets/d/12QmFW9vlx4ToHsQYGmkXK4aLD2jlI30FV1wYgxa0V8c/copy

It looks like this:

When I apply Query that should cut empty rows, my result table looks like this: It seems to cut all the rows without number value in Column A (strange!)

Note: Table is generated by a very long formula that searches multiple sheets. Whenever result is not found in one of sheets, formula returns empty row. So I need a solution to wrap around existing formula. Normally QUERY is a way to go, but not this time. I know that I can make additional step. Make one more sheet and use Filter:

=filter(Sheet1!A1:Z,Sheet1!E:E<>"")

Anyway this solution adds bulk to my spreadsheet.

For the moment my best solution is to take all the table contents and convert into strings before using query. Unfortunatelly I need numbers to be read as numbers. The reason: I make further calculations (it can be solved by converting it in calculation formula - so not a big deal). The other reason is that I use custom formatting that skips zeros. When zeros are strings, they are visible and make a mess. — Krzysztof Dołęgowski, Nov 17 '21 at 15:21

score 6 · Answer 1 · answered Nov 15 '21 at 18:05

6

If you convert Col E to text (TO_TEXT), you can run the query without worrying about mixed data:

=index(query({Sheet1!A:D,to_text(Sheet1!E:E),Sheet1!F:Z}, "select * where Col5 is not null ",0))

answered Nov 15 '21 at 18:05

Aresvik

4,484
1
5
18

Yes it works but I see more problems occuring. Query is changing data types in all the columns. Or changes it in an irregular way (depending on fitering results) And as @Erik Tyler said, it checks what data type is predominant. I can live with my column E as text, but it ignores some values in other columns too. I suppose that I can convert everything into text but it gets even more complicated as these tables are used for some calculations. So it would require converting it all again into numbers in calculation formulas. – Krzysztof Dołęgowski Nov 15 '21 at 18:31
Query works well as long as you don't have mixed data types. As per the example with Col E, you could transform the data in each column before you run the query, and either use TO_TEXT() to convert the column to text or N() to numbers. – Aresvik Nov 15 '21 at 18:53
1

There may be other (even simpler) ways to get the end result you want with mixed data types. But in the absence of being able to see the original spreadsheet and formula you're using prior to trying to apply the `QUERY` to weed out blanks, there's not much anyone can suggest further. Consider sharing a link to a copy of your sheet with the actual sheets and formula-in-question included (with personal information replaced with any filler data of the same type, if you like). – Erik Tyler Nov 15 '21 at 19:05

score 2 · Accepted Answer · answered Nov 15 '21 at 17:58

2

QUERY only returns the predominant data type per column. Your E column has mixed data types (strings and numbers, with numbers being predominant), so anything that is not a number will be a null — and thus ruled out by the QUERY.

As for how to solve it, that would be difficult to impossible to do given your sample spreadsheet only, since we can't see the actual formula that generates the initial output shown in sample Sheet1.

answered Nov 15 '21 at 17:58

Erik Tyler

9,079
2
7
11

Hi! Great suggestion! When I converted all the values in column E into text (using TO_TEXT inside formula that generates tables) query is working properly. – Krzysztof Dołęgowski Nov 15 '21 at 18:07
From there, if you need to use the numbers in further calculations, Sheets should be smart enough, once math operators are applied in a formula, to treat them as numbers. However, be aware that in pure comparisons (e.g., <= or => etc.) they are *strings*. So, for instance, while the *number* 21 is > the *number* 3, the *string* "21" is < the *string* "3" (i.e., anything starting with the string "2" is always less than anything starting with the string "3"). Just something to keep in mind. – Erik Tyler Nov 15 '21 at 18:54
Other functions such as `SUM` will also not recognize strings as numbers without help. So if, say, range E5:E50 were numbers-converted-to-strings, you could not use `=SUM(E5:E50)`. Rather, you'd need to use a math operator to help Sheets know that you mean them to be numbers, e.g., `=ArrayFormula(SUM(E5:E50*1))`. (While `SUM` with real numbers is, itself, an array formula, because you want to apply `*1` to every string in the range, you'd need to wrap the modified function in `ArrayFormula`.) – Erik Tyler Nov 15 '21 at 18:58

player0 · Answer 3 · 2021-11-17T15:39:57.527

there are ways but "short-formula lovers" will hate it... for example:

=ARRAYFORMULA(IF(ISNUMBER(QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)*1), 
 IFERROR(1/(1/QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)*1)), 
 QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)))

or:

=ARRAYFORMULA(IF(QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)<>"", 
 IF(ISNUMBER(QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)*1), 
 QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)*1, 
 QUERY(TO_TEXT(Sheet1!A:Z), "where Col5 is not null", 0)), ))

if you need zeros

or try like this:

=FILTER(Sheet1!A:Z, TRIM(FLATTEN(QUERY(TRANSPOSE(Sheet1!A:Z),,9^9)))<>"")

=FILTER(your_formula, TRIM(FLATTEN(QUERY(TRANSPOSE(your_formula),,9^9)))<>"")

Query formula ignores string values

3 Answers3