Sorting hyphenated numbers

[brendaxx]

I have a long string of numbers that I need to be able to sort from highest to lowest. They are in the following format: 9-100. The first number indicates one thing, and the second another, so the sorting must take both into account.

For example, in order:
9-100
9-150
10-7
10-35
10-90
11-1
11-10

All of the solutions I've found online are unsuitable for sorting numbers in this format. Any help please?

[63falcondude]

You can create two helper columns. If your data is in column A (starting in A1), try these:

B1 =LEFT(A1,FIND("-",A1)-1)+0
C1 =MID(A1,FIND("-",A1)+1,LEN(A1))+0

Now, highlight columns A:C > Data > Sort > Add Level > Sort by Column B Then by Column C > OK

[63falcondude]

Or, using a single helper column:

=(LEFT(A1,FIND("-",A1)-1)&RIGHT("000"&MID(A1,FIND("-",A1)+1,LEN(A1)),3))+0

[FDibbins]

Brenda, just so you know, those are not really numbers, they are text looking like a number. Any tile you have any non-numerical character in a number, that number becomes text, and excel treats it as it would any "abc-type" text. What the 2 above suggestions are doing, is taking that text and converting back tio a numeric

[63falcondude]

Brenda, just to be clear, the solutions provided in posts #'s 2 and 3 do not change the values in column A.

They convert the text values in column A into numerical values in another column/s. That other column/s is then used to sort on.

Also note that the solution in post #3 will only work when the value to the right of the dash is no more than 3 digits. Adjustments can be made if there can be numbers with more than 3 digits to the right of the dash.

[brendaxx]

Brenda, just to be clear, the solutions provided in posts #'s 2 and 3 do not change the values in column A.

They convert the text values in column A into numerical values in another column/s. That other column/s is then used to sort on.

Also note that the solution in post #3 will only work when the value to the right of the dash is no more than 3 digits. Adjustments can be made if there can be numbers with more than 3 digits to the right of the dash.

Thank you, that does what I need it to pretty well. There are a few cases of there being 4 digits to the right though. Does this solve it?
=(LEFT(A1,FIND("-",A1)-1)&RIGHT("000"&MID(A1,FIND("-",A1)+1,LEN(A1)),4))+0


Thank you also for the explanation, FDibbins.

[63falcondude]

Close. You have to include another 0 after the RIGHT function as well.

That is:

=(LEFT(A1,FIND("-",A1)-1)&RIGHT("0000"&MID(A1,FIND("-",A1)+1,LEN(A1)),4))+0

[brendaxx]

Thanks for your help with this problem.

[63falcondude]

You're welcome. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.