TEXTSPLIT UNIQUE TEXTJOIN

[Andrew__K]

I am trying to split text from a range (B2:B5), then re-join the unique values in one cell, separated by commas.

Ingredients	Allergen
Ingredient 1	Milk
Ingredient 2	Gluten, Soy, Milk
Ingredient 3
Ingredient 4	Milk

So far, I have below, which returns - Gluten, Soy, Milk, Milk. 

TEXTJOIN(", ",TRUE,UNIQUE(SORT(B2:B5)))

But when I add TEXTSPLIT in, it will only work when one cell is referenced.

TEXTJOIN(", ",TRUE,UNIQUE(SORT(TEXTSPLIT(B3,,","))))

Can anyone point me in the right direction to get this work for a range? Maybe I am missing something simple.

Thanks in advance.

[#2 · Accepted solution]

@Andrew__K Try this. It works "from the inside out".

=TEXTJOIN(", ",,SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,B2:B5),", "),TRUE),,,TRUE))

First join B2:B5, then split and make unique. Then sort and re-join. 

[#3]

@Riny_van_Eekelen perfect! thank you, thank you, thank you!