Forum Discussion

zalik22's avatar
zalik22
Copper Contributor
Sep 19, 2023

Help with sorting Sharepoint List

Hi, I have a sharepoint list that contains a combination of alpha and/or numeric characters. I am trying to sort descending by the numeric portion of the field. Any ideas? Some example values are: AB123 BR099 171 In this case, I want the record with 171 first, then AB123, then BR099. Thanks in advance. 

Lists

4 Replies

  • zalik22's avatar
    zalik22
    Copper Contributor
    Sep 21, 2023
    I created a new variable and used a combination of search, right, and if statements to get it to work. Thanks!
  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Sep 20, 2023

    Hi zalik22 

    you could create a new numeric column "SortColumn" and sort by that. Then use a PowerAutomate Flow to set values for the "SortColumn" like this (Assuming your original values are in the "Title" column):


    Use the formula 

    int(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(toUpper(triggerOutputs()?['body/Title']),'A',''),'B',','),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),',',''),' ',''))

    for the SortColumns.

    Best Regards,
    Sven

    PS:
    I could not get this calculation to work with a calculated column, which would be much better.
    But replacing multiple instances of a string in another string is not that easy..... If your string would have a pattern like "<Character><Character><Character><Number>" then we could try to develop a formula that just strips the first three characters from the string or something like that.

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor
    Sep 20, 2023

    zalik22 out of the box you can't sort on just the numeric part of a single line of text column as SharePoint has always sorted it from left to right. You would need separate columns for the numeric and text parts and sort on the number column. You could then have a calculated column that concatenates the text and number columns.

     

    You might not need this but for the number column Num I've ensured it has 3 numbers by formatting it with the following JSON:

     

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=padStart(toString(@currentField),3,'0')"
}

     

     

     

     

    The formula for the Combined column is =CONCATENATE(Txt," ",Num)

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

    • zalik22's avatar
      zalik22
      Copper Contributor
      Sep 20, 2023
      Thanks Rob, I don't need to worry about being 3 digits. I already have the field with numeric and text values. Can I create a variable and just put in code to extract the numeric values? How is this possible? I am not very familiar with SharePoint so if you could help me out that would be great!

Resources