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.   Ingredients Allergen Ingredient 1 Milk Ingredient 2 Gluten, Soy, Milk ...
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. 

  • Andrew__K's avatar
    Andrew__K
    Brass Contributor
    Feb 16, 2023

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