Forum Discussion

SunilD2007's avatar
SunilD2007
Copper Contributor
May 31, 2024
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...
excel
  • SnowMan55's avatar
    SnowMan55
    Jun 01, 2024

    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. 

SnowMan55's avatar
SnowMan55
Bronze Contributor
Jun 01, 2024

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. 

Resources