Sort, comma separated entries appearing in a cell, in ascending order

{5 Comments}

Assume a single column dataset as shown below.  As you can observe, there are multiple entries appearing the same cell separated by commas.

untitled

The objective is to sort, in ascending order, the entries in each cell.  The expected result is shown below.

untitled

I have solved this problem using Power Query a.k.a Data > Get & Transform in Excel 2016.  You may download my solution workbook from here.

RSS 5 Comments…

 Share your views
  1. Hi, it was a good challenge.
    I got the result by adding only one column. Hereafter the M code of this column:
    Text.Combine( List.Sort( List.ReplaceValue( Text.Split([Text], ","), " ", "", Replacer.ReplaceText
    ) ) ,", ")

    Thank you for all your articles

    • Hi,

      Thank you for posting. When I use the following code, I get an error saying "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

      let
      Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
      Result=Text.Combine(List.Sort( List.ReplaceValue(Text.Split([Text],","), " ", "", Replacer.ReplaceText)),", ")
      in
      Result

      Where am I going wrong.

      Thank you for sharing your knowledge.

  2. Hi Ashish,
    Hereafter the M code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Add column" = Table.AddColumn(#"Changed Type", "Custom", each
    Text.Combine(
    List.Sort(
    List.ReplaceValue(
    Text.Split([Text], ","), " ", "", Replacer.ReplaceText
    )
    )
    ,", ")
    )
    in
    #"Add column"

    Have a nice day 🙂

  3. Thanks to Power Query 😉

Leave a Comment

Your email address will not be published.

*

*