How to stop upper() from being pulled through as part of Google Sheets QUERY?

Question

I'm unable to find a way of making sure that upper() is not pulled through when using the QUERY function on Google, specifically with SELECT.

I have an example below:

=QUERY(countries, "SELECT UPPER(C)", 1)

But this outputs upper() as one of the cells. In this case the first item of the column is Continent so this pulls through as "upper(Continent)

Does anyone know how to fix this so that upper() doesn't appear at all?

Could you please give some references on _where_ in the example spreadsheet the formula can be found? Also please show the input, the expected result, and current result. — Pier Paolo Ramon, Mar 18 '20 at 12:29
It was highlighted in yellow bus as mentioned in my answer below, I managed to fix this. — Carlos Almeida, Mar 19 '20 at 19:26

score 1 · Answer 1 · answered Mar 18 '20 at 13:24

1

try:

=QUERY(QUERY(countries, "select upper(C)"), "offset 1", 0)

=QUERY(QUERY(countries, "select upper(C),upper(B)"), "offset 1", 0)

tho all you need is:

=ARRAYFORMULA(UPPER({C2:C, B2:B}))

or shorter:

=INDEX(UPPER({C2:C, B2:B}))

answered Mar 18 '20 at 13:24

player0

Thanks for your answer player0, but I managed to fix this using "label" – Carlos Almeida Mar 19 '20 at 19:27

score 0 · Accepted Answer · answered Mar 18 '20 at 11:27

0

Found the answer here!

Basically you need to label the column as nothing like so:

=QUERY(countries, "SELECT UPPER(C) label UPPER(C) ''", 1)

Hope this helps someone at some point as it took me a while to find!

answered Mar 18 '20 at 11:27

Carlos Almeida

2 Answers2