I hope you are all well. I am trying to sort a list of words in excel in a single column that has multiple words separated by a comma in a cell. I would like an output to be another delimited list sorted by word. So for example
| INPUT | OUTPUT | |
|---|---|---|
| Row 1 | Word 1, word 6, word 5 | Word 1, Word 5, word 6 |
| Row 2 | word 3, word 9, word 2, word 7 | Word 2, word 3, word 7, word 9 |
I have a list of all the possible words that can be in the delimited lists in a column. Is there a way to assign a number to the words and then have them organize based off the number?
Thanks,
CodePudding user response：
@P.b you'll have to let me know if this was what you had in mind. It took me forever to figure out the SORTBY part. There's gotta be a cleaner way.
=LET(myarray,FILTERXML("<x><y>"&SUBSTITUTE(A2,",","</y><y>")&"</y></x>","//y"),TEXTJOIN(",",TRUE,SORTBY(myarray,INDEX(Table3,MATCH(myarray,Table3[Fruit],0),{2}))))
CodePudding user response：
Using the same set-up and namings as @Bryan Rock, and assuming that Table3 is fixed so as to be sorted ascendingly on the Index column:
=TEXTJOIN(",",,IF(1-ISERR(SEARCH(","&Table3[Fruit]&",",","&A2&",")),Table3[Fruit],""))