How do I sort so that 1.2 comes before 1.10, while taking triple digit into consideration?

Hello,

I have a very similar question to another post that was resolved. However, the answer they got almost works for what I need, it does not take triple digit decimals into consideration, or at least does not work for them. The original thread is here:

http://www.excelforum.com/excel-gene...re-1-10-a.html

There was a solution posted on that page, that is as follows. It suggested making a "fake column" which can later be removed, using formula:

=--(LEFT(A1,SEARCH(".",A1))&RIGHT("0"&REPLACE(A1,1,SEARCH(".",A1),""),2))

Which seems to work almost perfectly. That is, if my numbers were only single and double digits after the decimal. However, if the number has both a number with 2 place decimal (like 45.01) and triple place (like 45.101) the formula drops off the first one in the second number, thus turning them both into 45.01.

I tried manipulating the code a bit to make that work, but it doesn't seem to put them in the right order, with the formula above, it seems to drop the first decimal digit, or I'm screwing it up. I cannot just leave things in the "correct" order, as another program I use reads these numbers. So I have to upload these files into the program in the order it accepts, which would really look like this (even though it's technically incorrect)

1.1
1.2
1.9
1.10
1.11
1.13
1.110
1.125
1.126
1.127
etc.

As the program itself only deals with the numbers in this format, and in this order. So right now I'm spending a bunch of time cutting and pasting the lines where they need to go. The formula above does sometimes help, but once i get a number with three digits after the decimal, every other number is in the incorrect order, so I have to do manually.

Any help would be outstanding.

Originally Posted by martindwilson

try
=LEFT(A1,FIND(".",A1)-1)&"."&TEXT(MID(A1,FIND(".",A1)+1,3),"000")

That did it! Geez, I wish I knew about this website earlier!