On Jun 1, 3:09 pm, Leslie W.
wrote:
Good Morning!

I searched through some previous posts and found some interesting answers to
different sorting questions, however, I could not find one exactly similar to
my situation.

I need to sort the following list:

12345.01
20052.01
12345.02.02
17789.02
17789.01
12345.01.03
12345.02

Excel sorts as follows:
12345.01
12345.02
17789.01
17789.02
20052.01
12345.01.03
12345.02.02

This would need to show in the following order:
12345.01
12345.01.03
12345.02
12345.02.02
17789.01
17789.02
20052.01

I understand the reason why I'm getting a funky sort is because the items
with two decimal points are being treated as text while the others are being
treated as numbers. One way I believe I got Excel 2003 to sort properly was
to create a new column, format it to be text (Format Cells Text), then
paste the row into the row that was previously formatted as text.

My data is saved from a sourse as a .csv file. I tried importing the data,
but it still treated some numbers as text.

Any ideas? Thanks!


The formulas will be simpler if your data have fixed widths, i.e.
first "." in position 6 and 2nd "." in 9.

Use an auxiliary column in B:B (assume that you start from A2).

=LEFT(A2,5)+MID(A2,7,2)/100+IF(MID(A2,9,1)=".",MID(A2,9,2)/10000,0)

Sort on column B:B

HTH
Kostis Vezerides