Excel LAMBDA Loop Through Each Column to CONCAT

[ExcelToDAX]

How can I loop through each column in a spilled range and CONCAT the values?

Ideally I want row 8 to be a spilled range instead of dragging the CONCAT function across the columns.

 

[StephenCrump]

What about: =BYCOL(A1#,LAMBDA(r,TEXTJOIN("",,r)))

If Sheet2!A12:D15 may contain blanks, you could change A1 to: =IF(Sheet2!A12:D15="","",Sheet2!A12:D15)

 

[Peter_SSs]

A minor thing but CONCAT (that you originally mentioned) is specifically designed to do what TEXTJOIN with "" delimiter does so I would suggest sticking with that.
(It is less to type as well )

=BYCOL(A1#,LAMBDA(r,CONCAT(r)))

If Sheet2!A12:D15 may contain blanks, you could change A1 to: =IF(Sheet2!A12:D15="","",Sheet2!A12:D15)

If there could be blanks in that range, then assuming either ..

• the non-blanks are text as in the example or
• the only use of this spilled array is this concatenation process (ie numbers do not need to remain numbers)

.. this could also be used

=Sheet2!A12:D15&""