Sorting numbered system that relies on decimals

  • Thread starter Thread starter Elliot Temple
  • Start date Start date
E

Elliot Temple

eg.
This is the result I am looking for:
TS-501.9.8
TS-501.9.9
TS-501.9.10
TS-501.9.11
TS-501.10.1
TS-501.10.2
TS-501.11.1

This is the result I am getting:
TS-501.10.1
TS-501.10.2
TS-501.11.1
TS-501.9.10
TS-501.9.11
TS-501.9.8
TS-501.9.9

As you can see the numbers between the dots ascend from 1, however
excel takes them as decimals (eg 0.1 = 0.10 = 0.100).

Suggestions appreciated!
 
Hi

It's possible to do using helping column, and a huge formula in it. But why
not to convert your column into form
TS-501.09.08
etc. Then you haven't any problems. The formula below (you can use it in
helping column also, when you vant) does this for value stored in cell A1

=LEFT(A1,FIND(".",A1))&TEXT(LEFT(C1,FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))
-1),"00")&"."&TEXT(RIGHT(C1,FIND(".";RIGHT(A1,LEN(A1)-FIND(".";A1)))-1),"00"
)
 
Thanks for the reply... didn't work, but I found another way. There wa
apparently an error in your equation :
 
Back
Top