CODEWITHSUNDEEP
excelexcel-formulaoffice365
Rasec Malkic
Rasec Malkic

Reputation: 671

How to concatenate words with a separator based on values in other column?

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

enter image description here

Upvotes: 1

Views: 1558

Answers (3)

Marcin Orlowski
Marcin Orlowski

Reputation: 75645

Instead of CONCAT you can use TEXTJOIN (docs) which is better designed for such concatenations, allowing for better control over delimiters and filtering out empty values (which is crucial for avoiding extra delimiters when column A has a 0):

=TEXTJOIN(" + ", TRUE, IF(A1:A7=1, B1:B7, ""))

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27338

Instead of CONCAT() use TEXTJOIN()

enter image description here

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

Upvotes: 2

Meet Patel
Meet Patel

Reputation: 327

In Excel 365, the best approach to achieve your expected output of concatenating the words associated with "1"s in column A, and separating them with " + ", is to use the TEXTJOIN function along with an array formula.

Here’s how you can adjust your formula to use TEXTJOIN:

  1. Place the following formula in a cell where you want the concatenated result to appear:

    =TEXTJOIN(" + ", TRUE, IF(A1:A7=1, B1:B7, ""))

  2. After typing in the formula, instead of just pressing Enter, press Ctrl+Shift+Enter. This will turn the formula into an array formula, which you can tell by the curly braces {} that will appear around the formula if you look at the formula bar. However, in newer versions of Excel 365, it might automatically treat it as an array formula without needing to press Ctrl+Shift+Enter.

Here’s what each part of the formula does:

  • TEXTJOIN(" + ", TRUE, ...): This function joins text items with a specified delimiter, which in your case is " + ". The second parameter TRUE tells Excel to ignore empty strings.
  • IF(A1:A7=1, B1:B7, ""): This part creates an array where only the cells in B1:B7 that correspond to a "1" in A1:A7 are included. If the corresponding cell in A is not "1", it puts an empty string in the array.

This formula will only include the words next to "1"s in your column A, and each will be separated by " + ", matching your expected output of "BROWN + WHITE + BLUE + GREEN".

Upvotes: 0

Related Questions