Sort combination of Text and Number

[Anonymous]

Hi Community,

I've this problem here. I'm trying to sort the item in my gallery based on the item's column Title in Share Point. The thing is my title is a combination of text and number such as LCP1, LCP2, LCP3, and so on. Let me illustrate as per picture below: 

For the purpose of this question, I've included the items' ID label which is referred from SharePoint list to make it clearer. From the image above, the item ID is not in order, but using the sort and filter function I've managed to sort the item based on Title (LCP1,LCP2,LCP3.etc.). 

The code used to sort the gallery is:  

SortByColumns(
Filter(
childItem,
MainCode.Id = Gallery2.Selected.ID && TextInput1_1.Text in Title
),
"Title",
Ascending
)

The issue is the code only works if the item does not exceed more than 10 (LCP10).  Once the item goes more than that (LCP11 - LCP19) , the system recognizes this value less than LCP2. See picture below for example if item exceeded 10.

Is there any workaround for this problem?  Truly need help for this problem as the items' title order is key feature in this app. Thanks!

[ArminSimon]

Hi,

this works as designed for text sorting, if you like to sort as you expected, I would split the text in two fields and extend the sort for those two field, or just use the two fields you already used to combine the title.

Please click Accept as Solution so that others may find it more quickly if this post solve your problem.
If you liked my response, please give it a Thumbs Up.

[v-xiaochen-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I've made a test for your reference:

1\ Add a button control and set its onselect property to:

ClearCollect(AAA,{Title:"TMF1"},{Title:"TMF10"},{Title:"TMF11"},{Title:"TMF2"},{Title:"LCP2"},{Title:"LCP3"},{Title:"LCP4"},{Title:"LCP5"},{Title:"LCP12"})

2\ Add a gallery control and set its Items property to:

SortByColumns( AddColumns(AddColumns(AAA,"Test",Value( Mid(ThisRecord.Title,4))),"Test2",Left(ThisRecord.Title,3)),"Test2",Ascending,"Test",Ascending)

3\ The result is as follows:

So, you could try this formula:

SortByColumns( AddColumns(AddColumns(Filter(

childItem,

MainCode.Id = Gallery2.Selected.ID && TextInput1_1.Text in Title

),"Test",Value( Mid(ThisRecord.Title,4))),"Test2",Left(ThisRecord.Title,3)),"Test2",Ascending,"Test",Ascending)

// The string length of "TMF" and "LCP" are both 3. 

If you have strings of other lengths, you need to use the If() function to judge.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.