SOLVED

TEXTSPLIT UNIQUE TEXTJOIN

Brass Contributor

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

 

IngredientsAllergen
Ingredient 1Milk
Ingredient 2Gluten, 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 Replies
best response confirmed by Andrew__K (Brass Contributor)
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. 

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

1 best response

Accepted Solutions
best response confirmed by Andrew__K (Brass Contributor)
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. 

View solution in original post