SOLVED

# TEXTSPLIT UNIQUE TEXTJOIN

Brass Contributor

# TEXTSPLIT UNIQUE TEXTJOIN

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.

2 Replies
best response confirmed by Andrew__K (Brass Contributor)
Solution

# Re: TEXTSPLIT UNIQUE TEXTJOIN

@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.

# Re: TEXTSPLIT UNIQUE TEXTJOIN

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

1 best response

Accepted Solutions
best response confirmed by Andrew__K (Brass Contributor)
Solution

# Re: TEXTSPLIT UNIQUE TEXTJOIN

@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.