I have in column A a list of 0’s and 1’s. In column B I have words.
| COL A | COL B |
|---|---|
| 1 | BROWN |
| 0 | BLACK |
| 0 | ORANGE |
| 1 | WHITE |
| 1 | BLUE |
| 1 | GREEN |
| 0 | GRAY |
I´d like to concatenate the words for those rows in column A that are equal to 1.
My current formula is =CONCAT(IF(A1:A7=1,B1:B7," + ")) and my current output is BROWN + + WHITEBLUEGREEN +
My expected output is BROWN + WHITE + BLUE + GREEN. I’m using Excel 365. Thanks
>Solution :
Instead of CONCAT() use TEXTJOIN()
=TEXTJOIN(" + ",1,IF(A2:A8,B2:B8,""))
Or, Could have used FILTER() with TEXTJOIN()
=TEXTJOIN(" + ",,FILTER(B2:B8,A2:A8))
Also, if you intent in using CONCAT() then you could use in this way, but it too much verbose uses one more function to parse the first delimiter.
=SUBSTITUTE(CONCAT(IF(A2:A8," + "&B2:B8,""))," + ",,1)