Sorting Decimal Style Numbers

[beachie2037]

Hi All,

Scratching my head trying to sort a large file based on a column of decimal style numbers that has been exported from another application.

I am on Win 7 running Excel 2010

Numbering sequence should be:
1, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.11, 1.12

but I have it all jumbled up and when I try to sort it I get:
1, 1.1, 1.11, 1.12, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9,

As is through hundreds of numbers on a number of sheets I dont really want to resort it all by hand!

Any ideas or suggestions greatly appreciated.

 

[PA HS Teacher]

You could try adding a helper column with the following pasted down, and sort by that column.

=INT(A1)&"."&TEXT(RIGHT(A1,LEN(A1)-(FIND(".",A1))),"000")

should turn
1.1 into 1.001
and 1.11 into 1.011

 

[mvptomlinson]

Or possibly just use RANK. In B1:

=RANK(A1,$A$1:$A$20)

Fill down to B20. Sort columns A & B by column B descending.

 

[PA HS Teacher]

Looking at my post again, the formula I provided will error if there is no decimal point. Try the following instead:
=IF(ISERROR(FIND(".",A1)),A1,INT(A1)&"."&TEXT(RIGHT(A1,LEN(A1)-(FIND(".",A1))),"000"))

As for using the Rank formula. If I understand the problem correctly, The Rank Function will rank also assume 1.9 > 1.11.

 

[ceranes]

I have a similar issue with a large list of drawing names.

[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]A0-0A[/TD]
 [/TR]
 [TR]
  [TD]A2-0[/TD]
 [/TR]
 [TR]
  [TD]A2-1.1[/TD]
 [/TR]
 [TR]
  [TD]A2-1.10[/TD]
 [/TR]
 [TR]
  [TD]A2-1.11[/TD]
 [/TR]
 [TR]
  [TD]A2-1.12[/TD]
 [/TR]
 [TR]
  [TD]A2-1.13[/TD]
 [/TR]
 [TR]
  [TD]A2-1.14[/TD]
 [/TR]
 [TR]
  [TD]A2-1.15[/TD]
 [/TR]
 [TR]
  [TD]A2-1.16[/TD]
 [/TR]
 [TR]
  [TD]A2-1.17[/TD]
 [/TR]
 [TR]
  [TD]A2-1.18[/TD]
 [/TR]
 [TR]
  [TD]A2-1.19[/TD]
 [/TR]
 [TR]
  [TD]A2-1.2[/TD]
 [/TR]
 [TR]
  [TD]A2-1.20[/TD]
 [/TR]
 [TR]
  [TD]A2-1.4[/TD]
 [/TR]
 [TR]
  [TD]A2-1.5[/TD]
 [/TR]
 [TR]
  [TD]A2-1.6[/TD]
 [/TR]
 [TR]
  [TD]A2-1.7[/TD]
 [/TR]
 [TR]
  [TD]A2-1.8[/TD]
 [/TR]
 [TR]
  [TD]A2-1.9[/TD]
 [/TR]
 [TR]
  [TD]A2-2[/TD]
 [/TR]
 [TR]
  [TD]A2-3.1[/TD]
 [/TR]
 [TR]
  [TD]A2-3.2[/TD]
 [/TR]
 [TR]
  [TD]A2-3.3[/TD]
 [/TR]
 [TR]
  [TD]A2-3.4[/TD]
 [/TR]
 [TR]
  [TD]A2-4[/TD]
 [/TR]
 [TR]
  [TD]A2-5[/TD]
 [/TR]
 [TR]
  [TD]A4-1[/TD]
 [/TR]
 [TR]
  [TD]A4-2[/TD]
 [/TR]
 [TR]
  [TD]A4-3[/TD]
 [/TR]
 [TR]
  [TD]A4-4[/TD]
 [/TR]
 [TR]
  [TD]A5-1[/TD]
 [/TR]
 [TR]
  [TD]A5-2[/TD]
 [/TR]
 [TR]
  [TD]A5-3[/TD]
 [/TR]
 [TR]
  [TD]A5-4[/TD]
 [/TR]
 [TR]
  [TD]A5-5[/TD]
 [/TR]
 [TR]
  [TD]A5-6[/TD]
 [/TR]
 [TR]
  [TD]A5-7[/TD]
 [/TR]
 [TR]
  [TD]A5-8[/TD]
 [/TR]
 [TR]
  [TD]A5-9[/TD]
 [/TR]
 [TR]
  [TD]A6-4[/TD]
 [/TR]
 [TR]
  [TD]C0-1[/TD]
 [/TR]
 [TR]
  [TD]C0-2[/TD]
 [/TR]
 [TR]
  [TD]C0-3[/TD]
 [/TR]
 [TR]
  [TD]C1-1[/TD]
 [/TR]
 [TR]
  [TD]C2-1[/TD]
 [/TR]
 [TR]
  [TD]C2-2[/TD]
 [/TR]
 [TR]
  [TD]C2-3[/TD]
 [/TR]
 [TR]
  [TD]C2-4[/TD]
 [/TR]
 [TR]
  [TD]C2-5[/TD]
 [/TR]
 [TR]
  [TD]C2-6[/TD]
 [/TR]
 [TR]
  [TD]C3-1[/TD]
 [/TR]
 [TR]
  [TD]C3-2[/TD]
 [/TR]
 [TR]
  [TD]C3-3[/TD]
 [/TR]
 [TR]
  [TD]C3-4[/TD]
 [/TR]
 [TR]
  [TD]C3-5[/TD]
 [/TR]
 [TR]
  [TD]C3-6[/TD]
 [/TR]
 [TR]
  [TD]C3-7[/TD]
 [/TR]
 [TR]
  [TD]C4-1[/TD]
 [/TR]
 [TR]
  [TD]C4-2[/TD]
 [/TR]
 [TR]
  [TD]C4-3[/TD]
 [/TR]
 [TR]
  [TD]C4-4[/TD]
 [/TR]
 [TR]
  [TD]C4-5[/TD]
 [/TR]
 [TR]
  [TD]C4-6[/TD]
 [/TR]
 [TR]
  [TD]C5-1[/TD]
 [/TR]
 [TR]
  [TD]C5-2[/TD]
 [/TR]
 [TR]
  [TD]S0-1[/TD]
 [/TR]
 [TR]
  [TD]S0-2[/TD]
 [/TR]
 [TR]
  [TD]S2-0.1[/TD]
 [/TR]
 [TR]
  [TD]S2-0.2[/TD]
 [/TR]
 [TR]
  [TD]S2-0.3[/TD]
 [/TR]
 [TR]
  [TD]S2-0.4[/TD]
 [/TR]
 [TR]
  [TD]S2-0.5[/TD]
 [/TR]
 [TR]
  [TD]S2-1.1[/TD]
 [/TR]
 [TR]
  [TD]S2-1.10[/TD]
 [/TR]
 [TR]
  [TD]S2-1.11[/TD]
 [/TR]
 [TR]
  [TD]S2-1.12[/TD]
 [/TR]
 [TR]
  [TD]S2-1.13[/TD]
 [/TR]
 [TR]
  [TD]S2-1.14[/TD]
 [/TR]
 [TR]
  [TD]S2-1.15[/TD]
 [/TR]
 [TR]
  [TD]S2-1.16[/TD]
 [/TR]
 [TR]
  [TD]S2-1.17[/TD]
 [/TR]
 [TR]
  [TD]S2-1.18[/TD]
 [/TR]
 [TR]
  [TD]S2-1.19[/TD]
 [/TR]
 [TR]
  [TD]S2-1.2[/TD]
 [/TR]
 [TR]
  [TD]S2-1.20[/TD]
 [/TR]
 [TR]
  [TD]S2-1.4[/TD]
 [/TR]
 [TR]
  [TD]S2-1.5[/TD]
 [/TR]
 [TR]
  [TD]S2-1.6[/TD]
 [/TR]
 [TR]
  [TD]S2-1.7[/TD]
 [/TR]
 [TR]
  [TD]S2-1.8[/TD]
 [/TR]
 [TR]
  [TD]S2-1.9[/TD]
 [/TR]
 [TR]
  [TD]S2-2[/TD]
 [/TR]
 [TR]
  [TD]S2-3[/TD]
 [/TR]
 [TR]
  [TD]S3-1[/TD]
 [/TR]
 [TR]
  [TD]S3-2[/TD]
 [/TR]
 [TR]
  [TD]S3-3[/TD]
 [/TR]
 [TR]
  [TD]S3-4[/TD]
 [/TR]
 [TR]
  [TD]V0-1[/TD]
 [/TR]
 [TR]
  [TD]V0-2[/TD]
 [/TR]
 [TR]
  [TD]V0-3[/TD]
[/TR]
</tbody>[/TABLE]

The order in which Excel sorted them is incorrect. Those with actual decimals should be listed in order, such as 1.9, 1.10, 1.11, 1.12, etc. Not 1.1, 1.10. 1.11...1.19, 1.2, 1.20, etc.

I don't quite see how the solutions here help, but maybe I am missing something.

Thanks,
Chris