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 Information |
| None |
| None |
| None |
| Milk |
| Gelatine |
| None |
| None |
| Cochineal |
| Gluten |
| None |
| None |
| None |
| None |
I want my single cell to give me this: Milk, Gelatine, Cochineal, Gluten
I used the below formula:
=CONCATENATE(IF(K23="None","",K23), IF(K24="none","",K24), IF(K25="none","",K25), IF(K26="none","",K26), IF(K27="none","",K27), IF(K28="none","",K28), IF(K29="none","",K29), IF(K30="none","",K30), IF(K31="none","",K31), IF(K32="none","",K32), IF(K33="none","",K33), IF(K34="none","",K34), IF(K35="none","",K35))
and I got:
| MilkGelatineCochinealGluten |
• I want the words to be separated with a comma and space.
Lastly, I want to add on the formula something that will give me:
- the word none if all my allergen collumn has the word none on every cell,
- ignore any duplicate or tricplicate and so on words that are not the word none.
So for example if my column had:
none
none
milk
none
gluten
none
none
gluten
gluten
I want to be able to get: milk, gluten
I really hope someone can help out! 😞
Many thanks in advance,
Lilian
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"))
