'how to concatenate the names selected in google sheet?

enter image description here

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!

google-sheetsconcatenationformula


Solution 1:[1]

See if this helps

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

enter image description here

Solution 2:[2]

=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 " + ".

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JPV
Solution 2 MattKing
Solution 3 a-burge

Related Questions