Numerical Sorting

[Carol]

I have a numerical sorting issue with project numbers that sort as follows
(not in correct numerical order):

2008-01
2008-010
2008-0100
2008-02
2008-1
2008-2
2008-200
2008-3

The problem is that Excel sorts by the first number it sees - so 010 would
come before 1.

 

[Pete_UK]

Can you arrange for those numbers after 2008- to have the same number
of digits, by adding leading zeros like this:

2008-0001
2008-0010
2008-0100
2008-0002
2008-0001
2008-0002
2008-0200
2008-0003

If so, then they would sort correctly.

You can put the leading zeros in using a formula like this:

=LEFT(A1,5)&TEXT(RIGHT(A1,LEN(A1)-5),"0000")

assuming the number is in A1 - copy down as required.

Hope this helps.

Pete