Google Sheets formula to merge multiple columns into one cell, including headers / label-CodePudding

I want to combine multiple columns in a Google sheet into one cell, including headers / labels. My input sheet looks like this:

Name	Description	Col 1	Col 2	Col 3
Foo		val 1	val 2	val 3
Bar		val x	val y	val z

And I want to concatenate the values in each column into a single cell, including the headers for each value. Desired output:

Name	Description
Foo	Col 1 val 1 Col 2 val 2 Col 3 val 3
Bar	Col 1 val x Col 2 val y Col 3 val z

This formula concatenates the columns into one, however it doesn't include the headers (note that char(10) produces a linebreak):

=TEXTJOIN(char(10),true,B2:E2)

I can produce a similar result using

or in one cell if that's what you want:

=INDEX(TRIM(REDUCE(, B1:D1&CHAR(10)&B2:D3&CHAR(10), 
 LAMBDA(a, c, a&c&CHAR(10)))))

CodePudding user response：

Use this formula

=ARRAYFORMULA( 
 QUERY({"Description"; 
        TRIM(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
        IF(B2:D="",,","&B1:D1&","&B2:D&",")),"",9^9)),",",CHAR(10)))},
        " Where Col1 is not null" )       )