I have a range of values and empty cells A2:F15
I want to concatenate only non empty cells using a single formula Arrayformula
, Make a copy of this example sheet.
I created this formula but it's not an array based
=TEXTJOIN(", ",,TRANSPOSE(QUERY(FLATTEN(A2:F2)," where Col1 is not null ")))
use query smash:
=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(A2:F="",,A2:F&",")),,9^9))), ",$", ))