Forum Discussion
Solved
How to normalize comma separated values that are the same but were loaded in a different order
Hello, looking for some help. Is there an easy way to normalize the following table? The values in column B are all the same, they were just loaded in a different order. Is there a way to mass upd...
SunilD2007 Yes, an easy solution is available if you are using Excel 365 or Excel for the web (as the TEXTSPLIT function is used). Assuming that each word/term is separated by a comma and a space:
=TEXTJOIN(", ", TRUE, SORT( TRANSPOSE( TEXTSPLIT(B2, ", ") ) ) )(Except for the space in the literals, spaces are for increased readability, and are not required.)
See the attached workbook.
SunilD2007 Try this single cell dynamic spill array formula-
=MAP(B2:INDEX(B2:B50000,COUNTA(B2:B50000)),
LAMBDA(x,TEXTJOIN(", ", 1, SORT(TEXTSPLIT(x, ,", ") ))))
Thank you, Harun24HR. I actually used the suggestion from SnowMan55 and that worked perfectly. Thank you for taking the time to respond!