Sorting ascending in proper numerical format for a range

[WxShady13]

I have a LOS Range field (0-1,2-3,4-5,6-7, etc) that I need to sort properly. I have it as a text field so it will show the range but I do not have another field that I can use to sort it numerically. How do I get it to list in proper order?

 

[Joe4]

If you go over 10, then it will not sort correctly, because in alphabetic sorting, values like: "10-11" will come before "2-3".

One way around this is to create a "helper" column that pulls off the first number, and sort by this.
So, if you enteries were in column A, starting on row 2, you could put this formula in any blank cell in row 2:
=LEFT(A2,FIND("-",A2)-1)+0
and copy down for all rows.

Then, use this column for your sorting.
If you do not want to display it, you can hide this column.

 

[sandy666]

I have a LOS Range field (0-1,2-3,4-5,6-7, etc)

post representative example and expected result using XL2BB

 

[WxShady13]

My range column is only present in the Power BI environment and not excel. I tried this formula
LOSHelper = left(Table1[LOS Range],Find("-",Table1[LOS Range])-1)+0 but it comes back with "The search Text provided to function 'Find' found not be found in the given text." Any idea what is causing that?

 

[sandy666]

so post a picture (ugh!) of you source data from Editor

 

[WxShady13]

Here is what the LOS Range field looks like

 

[sandy666]

did you read post#5 ?

or post a link to the shared pbix file using onedrive, googledrive, dropbox or any similar

 

[Joe4]

My range column is only present in the Power BI environment and not excel

My apologies, I missed that fact.

 

[WxShady13]

My apologies, I missed that fact.

Thank you for your help.

 

[WxShady13]

did you read post#5 ?

or post a link to the shared pbix file using onedrive, googledrive, dropbox or any similar

Is this what you mean?