Search code examples
google-sheetssplitgoogle-sheets-formula

Google Sheets, splitting cell values within a Query?

(Related to this question)

I want to split the values in each cell, that is either blank or contains one or more comma-separated tags. Can I do this from within the QUERY? Or, how would I copy the column to a scratch column that is longer because the cell values are split into one or more columnar values?

This formula works nicely to show tags and counts, but treats each cell as a single text value:

=QUERY(Notes!D1:D, "Select D, count(D) 
      where D matches '^(?!(?:Labels|Tags)$).+' 
       group by D order by count(D) DESC label count(D) ''")

I also have this formula, which returns an array of non-blank, comma-separated values in a range:

=ArrayFormula(SPLIT(filter(Notes!D1:D, not(isblank(Notes!D1:D))), ","))

But this also has the problem that it splits values across columns (instead of rows), so I can't use the results as a simple range.

I have tried wrapping occurences of D, the data column, with the ArrayFormula. Each time I get a #VALUE! error from QUERY.

Solution

  • For what I get you're trying to do, you may find useful to FLATTEN your range and make it all in one column:

    =FLATTEN(ArrayFormula(SPLIT(filter(Notes!D1:D, not(isblank(Notes!D1:D))), ",")))

    enter image description here

    Just if needed, you can add TRIM too so you don' have undesired spaces:

    =FLATTEN(ArrayFormula(TRIM(SPLIT(filter(Notes!D1:D, not(isblank(Notes!D1:D))), ","))))

    I don't know what your purpose then is, but you can wrap this in a QUERY to count as you expressed in your post too. Since it's a new column, you should name that column Col1:

    =QUERY(FLATTEN(ArrayFormula(TRIM(SPLIT(filter(Notes!D1:D, not(isblank(Notes!D1:D))), ",")))),"Select Col1,COUNT(Col1) group by Col1 order by count(Col1) DESC label count(Col1) ''",)

    enter image description here