1

Table with data in columns A-N. Data in column 'M' contains CSV data.

Using this query can return data, need what's in 5th returned column (E) to be split into additional columns through one command. Cannot do this separately manually...

=QUERY(scores,"select A,H,K,N,M WHERE N is not null and M is not null order by K ASC, N DESC, H ASC, A DESC")

Output has to be obtained using QUERY function, as its pulling from a named-range, from a data set I cannot modify. The other columns may contain commas and are not required to be split up.

Currently the column required to be split comes from M in the original named range. When this query runs, the data needed to be split is in the 5th column (E).

Not clear how to augment what you've suggested to fit this. I was able to use your example of offset to hit the named range, but that omits the necessary limits imposed by the SQL select portions of QUERY.

1
  • Please update your question to include this info.
    – Blindspots
    Commented Dec 1, 2022 at 5:30

1 Answer 1

1

It's not entirely clear why you insist on using QUERY().

Perhaps

=ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")})

will do the job?

Finally, you can query the result of this expression -

=QUERY(ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")}))
or something similar
=QUERY(ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")}),
       "select * where Col4 = 333",1)

Update The literal translation of the formula given in the question looks like this:

=QUERY(ARRAYFORMULA({OFFSET(scores,0,0,,1),
                     OFFSET(scores,0,7,,1),
                     OFFSET(scores,0,10,,1),
                     OFFSET(scores,0,13,,1),
                     SPLIT(OFFSET(scores,0,12,,1),",")}),
    "select * WHERE Col4 is not null and (Col5 is not null or Col6 is not null) 
      order by Col3 ASC, Col4 DESC, Col2 ASC, Col1 DESC")
1
  • Seems original questions was too obfuscated to make sense... Added to the original question with clarifying info. Tried using just offset/split and was able to hit the original data with that, but it omitted the SQL Query portion, and I'm not clear how to bridge those together. Commented Dec 2, 2022 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.