Forum Discussion
Concatenating arguments with the IF function
I'm using MS Excel 2016 (part of Office Professional Plus 2016)
I have following spreadsheet.
ID | plan1 | plan2 | plan3 | plan4 | plan5 | plan6 | Result |
12345 | BH1 | DoNothing | BH3 | DoNothing | DoNothing | DoNothing | BH1;BH3 |
12255 | DoNothing | DoNothing | DoNothing | DoNothing | DoNothing | BH6 | BH6 |
12667 | DoNothing | DoNothing | BH3 | BH4 | DoNothing | DoNothing | BH3;BH4 |
13984 | BH1 | BH2 | BH3 | BH4 | BH5 | BH6 | BH1;BH2;BH3;BH4;BH5;BH6 |
13999 | BH1 | DoNothing | DoNothing | BH4 | BH5 | BH6 | BH4;BH5;BH6 |
14016 | DoNothing | DoNothing | DoNothing | BH4 | DoNothing | BH6 | BH4;BH6 |
14555 | DoNothing | BH2 | DoNothing | DoNothing | BH5 | DoNothing | BH2;BH5 |
14578 | DoNothing | DoNothing | DoNothing | DoNothing | DoNothing | BH6 | BH6 |
14580 | DoNothing | DoNothing | DoNothing | DoNothing | DoNothing | DoNothing |
In column H, I want to have a concatenated list of the data in all columns, separated by a semicolon, for a given row where the row does not have "DoNothing". So the data only concatenates into the end result field if it is not "DoNothing". Also would prefer to not have the semicolon after the last item in the result, but that is the least of my worries.
I've tried combining the Concatenate and IF functions like this: =CONCATENATE(=IF(B2<>"DoNothing",B2 & ";",""),=IF(B3<>"DoNothing",B3 & ";",""))
I'm trying to get the end result by using criteria based on a range of fields (IF B2:G2<>"DoNothing"), then concatenate the data from that field into the end result, separated by a semicolon.
My thought was to concatenate the result of both IF statements, but it doesn't work. If I use the IF statement by itself, it does work, but I need to concatenate it with the other columns in the result.
Is there a better way to get this to work, instead of doing the IF statement individually for each column? I'm thinking there has to be a way.
Detlef_Lewin Thank you so much Detlef. This worked perfectly. I wasn't aware of the TEXTJOIN function and didn't see it with all of my web searches. I'll keep it handy.
- Detlef_LewinSilver Contributor
- Bogie27Copper Contributor
Detlef_Lewin Thank you so much Detlef. This worked perfectly. I wasn't aware of the TEXTJOIN function and didn't see it with all of my web searches. I'll keep it handy.
- HalloWorldCopper Contributor
This answer gives you the chance to change the second "B2:G2" to another row and check against empty cells. I changed it to the header row to join all of the headings as soon as the cell was not empty: `=TEXTJOIN(";",TRUE,IF(B2:G2<>"",B$1:G$1,""))`. You need to execute it with `Ctrl+Shift+Enter`.
- Hogstad_RaadgivningSteel ContributorI Will also suggest TextJoin
=TEXTJOIN(";",TRUE,SUBSTITUTE(C3:H3;"DoNothing";""))&";"