CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Allan Bech
Allan Bech

Reputation: 395

How can I split a result from a query into two columns

In this example I collect data from a Google Form and output the results with a query. I need to split the result in two parts. I have tried to combine my formula with SPLIT but that didn't work well. How can I do it?

={"Faglig vurdering for "& SUM(COUNTIF('Formularsvar 1'!C2:C; "*9M*"))&" elever i 9M";
 QUERY(ARRAYFORMULA(IF((LEN('Formularsvar 1'!D2:D))*('Formularsvar 1'!C2:C="9M");
 ('Formularsvar 1'!D2:D&CHAR(10)&
  'Formularsvar 1'!E2:E&CHAR(10)&
  'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "&
  'Formularsvar 1'!G1&" "&'Formularsvar 1'!$G$2:G&" "&
  'Formularsvar 1'!H1&" "&'Formularsvar 1'!$H$2:H&" "&
  'Formularsvar 1'!$I$2:I&CHAR(10)); ));"where Col1 is not null ORDER BY Col1"; 0)}

Upvotes: 0

Views: 106

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

You could use SPLIT to separate the two columns. Something like this:

=QUERY(ARRAYFORMULA(IF((LEN('Formularsvar 1'!D2:D))*('Formularsvar 1'!C2:C="9M");ARRAYFORMULA(SPLIT('Formularsvar 1'!D2:D&"|||"& 'Formularsvar 1'!E2:E&CHAR(10)& 'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "& 'Formularsvar 1'!G1&" "&'Formularsvar 1'!$G$2:G&" "& 'Formularsvar 1'!H1&" "&'Formularsvar 1'!$H$2:H&" "& 'Formularsvar 1'!$I$2:I&CHAR(10);"|||"));));"where Col1 is not null ORDER BY Col1";0)

Note:

  • ||| here is used as a separator for SPLIT. Please replace it with your favorite separator :)

Upvotes: 1

Related Questions