Forum Discussion

Andrew__K's avatar
Andrew__K
Brass Contributor
Feb 16, 2023

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.

 

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.

Excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Feb 16, 2023

    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's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 16, 2023

    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. 

Share

Resources