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

Andy Ray 0
Viewed 85

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 REDUCE (I can't believe I'm writing a reducer in a Google Sheet):

=REDUCE("", B2:E2, LAMBDA(accumulator, current_value, CONCATENATE(accumulator, current_value, char(10))))

However the lambda function doesn't seem to provide the index of the current iteration, so I'm not sure how to grab the column name above it.

google-sheets-formula google-sheets
3 Answers

try the old way:

=ARRAYFORMULA(TRIM(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(B2:D="",,"×"&B1:D1&"×"&B2:D&"×")),,9^9))), "×", CHAR(10))))

enter image description here

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

enter image description here

player00

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

enter image description here

Osm0

Using your current TEXTJOIN() formula you can just include the Column Headers with the dollar sign ($) which means absolute reference so it will not change when copied/dragged down to rows below.

Try:

=TEXTJOIN(char(10),true,C$1,C2,char(10),D$1,D2,char(10),E$1,E2)

Drag down to rows.

Result:

enter image description here

Refer to this link for more info on Absolute Reference: https://www.lido.app/tutorials/absolute-reference-on-google-sheets

Dan F0
Related