Trouble sorting imported data

**LRuben** · 03-20-2013, 07:36 PM

I can't figure out how to get the line #s to sort properly (lines 1.100, 1.101, 1.104 should be at the bottom of the list). I import this data, it comes through as text. I have converted the field to numbers and tried all sorts of functions but no matter what I do the sort remains in this order. It feels like there has to be a systematic way to get this sorted properly.

Quote Line Number
805571 1.10
805571 1.100
805571 1.101
805571 1.104
805571 1.12
805571 1.13

**martindwilson** · 03-20-2013, 07:47 PM

assuming 1.10 is in b1 and b1 is text
then in c1 or another column put

=TEXT(INT(B1),"0000")&"."&TEXT(MID(B1,FIND(".",B1)+1,3),"000")

fill down and use that column to sort by
it sorts as
1.10
1.12
1.13
1.100
1.101
1.104