Forum Discussion
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 update so that these appear in the same order. The file itself contains many combinations...for example, there may be cells that have 3 comma separated values but each value is the same, just in a different load order. Or 4 values, in a different load order. Thank you in advance!!
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 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.
- Thanks SnowMan55! This worked perfectly! Really appreciate the fast response and the accurate solution!
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!
