1

I have this table : enter image description here

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 ?

2 Answers 2

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)
1

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.