Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querytextjoin

Adding an empty value between joined ranges using arrayformula


I want to join each value in a range with another selected range (array), but adding an empty value between them for better readability.

values to join: {"A","B","C"}
<0>
A   B
CAT <formula goes here>
DOG

<1>
A   B
CAT CAT_A
DOG CAT_B
    CAT_C
    DOG_A
    DOG_B
    DOG_C

<2>
A   B
CAT CAT_A
DOG CAT_B
    CAT_C

    DOG_A
    DOG_B
    DOG_C

So I did <1> with

=ArrayFormula(TRANSPOSE(split(textjoin("|",1,(concat(filter([range],[range]<>""),"_"&{"A","B","C"}))),"|")))

but I don't know how to add an empty value to make <2>.


Solution

  • =ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(SPLIT(TEXTJOIN("|", 1, 
     FILTER("♦"&A4:A, A4:A<>"")&"_"&{"A", "B", "C♦"}), "|")),,999^99), "♦"))))
    

    0