Can we do arrayformula for repeating horizontal join?

Question

I have this table :

where i want to join all data in each row start from column D to last column. I want the output just like in that C2:C5

I use this formula in C2 :

=JOIN(",",filter(D2:2, D2:2<>""))

and copy paste down for C3:C5. This is working just fine but i'm looking the way to make it as a single formula using arrayformula() in C2.

Is it possible ?

Answer 1

This approach doesn't use the ARRAYFORMULA function but instead uses BYROW and COUNTA to skip empty rows.

=BYROW(D2:100, LAMBDA(r, 
    IF(COUNTA(r), JOIN(",",TOROW(r,1)),
      IFNA(#N/A))))

If you want to remove empty rows you can wrap it TOCOL like this:

=TOCOL(BYROW(D2:100, LAMBDA(r, 
    IF(COUNTA(r), JOIN(",",TOROW(r,1)),
      IFNA(#N/A)))),1)

Answer 2

Classic way

Use a query smush like this:

=arrayformula( 
  substitute( 
    trim( 
      transpose( 
        query( transpose(D2:ZZ), "", 9^9 ) 
      ) 
    ), 
    " ", 
    ", " 
  ) 
)

Modern way

When the data contains spaces, the query smush pattern requires another two substitute()s to avoid them getting replaced with commas. Lambda functions let you avoid that by iterating the data row by row, like this:

=let( 
  joinRows_, lambda(d,c,byrow(c,lambda(r,if(counta(r),textjoin(d,1,r),)))), 

  joinRows_(", ", A2:C) 
)

See lambda() and byrow().