ArrayFormula with TextJoin of a row of columns-CodePudding

I have a table as follows:

I would like to use the following formula =SPLIT(TEXTJOIN(",",TRUE, Data!B2:C2),",") with ArrayFormula, but I am not sure how I should adapt B2:C2 to work with the function.

Expected output (result of dragging formula down):

CodePudding user response：

Use the Ampersand Operator (&) Instead

The function TEXTJOIN() does not work with ARRAYFORMULA(). Instead, you may use the Ampersand Operator (&) expression to join the texts together. In your case, you may use the following:

=ARRAYFORMULA(split(arrayformula(B2:B7 & "," & C2:C7),","))

Sample Output

Based from the output below, your desired result was met.