how to concatenate the names selected in google sheet?-CodePudding

Hi everyone,

I want to use a formula to concatenate the name that has been selected in the checkbox by using the delimiter " ". The expected output is highlighted in yellow in the screenshot above. Ideally the formula can be an ARRAYFORMULA in cell F5 instead of making entire column F full of formula. Any help or advice will be greatly appreciated!

CodePudding user response：

See if this helps

=ArrayFormula(substitute(trim(transpose(query(transpose(if(B5:E, B4:E4,)),,9^9))), " ", "   "))

CodePudding user response：

=ARRAYFORMULA(SUBSTITUTE(TRIM(IF(B5:B,B4,)&CHAR(10)&IF(C5:C,C4,)&CHAR(10)&IF(D5:D,D4,)&CHAR(10)&IF(E5:E,E4,)),CHAR(10),"   ")

This is a fairly simple way to do it that may be easier to understand than a query smush. The extra CHAR(10)'s [carriage returns] get chopped off the ends and de-duplicated in the middle by the TRIM(). Then the remaining ones are replaced with " ".

CodePudding user response：

Not an arrayformula but in my opinion a simpler formula to use in each row: =TEXTJOIN(" ",true,arrayformula(if(B3:E3,$B$2:$E$2,"")))