Forum Discussion
Solved
Concatenate IF help
Hi everyone, I have been trying to use concatenate with the IF function to generate product allergens by ignoring the word none. I basically have: Column K has the below Allergen Informat...
Aaargh, it's not my day. Wrong order of the functions.
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(K23:K35,K23:K35<>"None","None")))
Do you have Excel in Office 2019, Office 2021 or Microsoft 365? If so, you can use TEXTJOIN:
=TEXTJOIN(", ", "", IF(K23:K35="None", "", K23:K35))
In Excel 2019, confirm the formula with Ctrl+Shift+Enter.
If you have Excel in Office 2021 or Microsoft 365, you can also use
=TEXTJOIN(", ", "", FILTER(K23:K35, K23:K35<>"None"))
Added: sorry, the second argument of TEXTJOIN should have been TRUE instead of "".
- Hi there, thanks for the quick reply!
I have Excel in Microsoft 365, tried the formula you suggested and I get a #VALUE! error 😞My apologies, it should have been
=TEXTJOIN(", ", TRUE, IF(K23:K35="None", "", K23:K35))
=TEXTJOIN(", ", TRUE, FILTER(K23:K35, K23:K35<>"None"))
- Ok so
1) =TEXTJOIN(", ", TRUE, IF(K23:K35="None", "", K23:K35))
duplicate allergens still appear and when column K is changed to have none in all the cells I get a #CALC! error
2) =TEXTJOIN(", ", TRUE, FILTER(K23:K35, K23:K35<>"None"))
duplicate allergens still appear and when column K is changed to have none in all the cells the cell is just empty
Is there a way to incorporate another function in this TEXTJOIN you gave me where it will give
all the alergens separated by a comma and space as well as:
- the word none if all my allergen collumn has the word none on every cell,
- ignore any duplicate or triplicate allergens
Or is there any other function that can do the above? 😞
