Excel 2007 : Sorting column by numbers, lowest to highest

**elryp3000** · 06-06-2012, 06:35 PM

Hello Forum,

When I sort the data in a numbered cell from lowest to highest, I run into a problem. Numbers 2012-1 through 2012-10 are fine. However, the next number after 2012-10 is 2012-100 instead of 2012-11. Number 2012-11 does now show up until after number 2012-109. The next number following is number 2012-110.

Example:

2012-1
2012-2
2012-3
2012-4
2012-5
2012-6
2012-7
2012-8
2012-9
2012-10
2012-100
2012-101
The numbers are not stored as text.

How can I get the numbers to sort from 1 to 500 sequentially?

**JBeaucaire** · 06-06-2012, 06:41 PM

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. We need to see what these really are.

I'm going to guess you will need a helper column to "reformat" that data into strings that are all the same length:

2012-1 >>>> 2012-001
2012-101 >>> 2012-101

You can see how Excel will have no problem sorting by the new format, then you can delete that helper column.

**elryp3000** · 06-06-2012, 07:16 PM

Here is a sample of what i am speaking about. Due to security reasons, it is not a good idea to upload that actual file i am working on.

**JBeaucaire** · 06-07-2012, 12:40 AM

1) Put this formula in C2, then copy it down the whole dataset:

=LEFT(A2,4)&"-"&TEXT(MID(A2, FIND("-", A2)+1, 4),"000")

2) Highlight columns A:C

3) Sort those columns based on column C

4) Clear the helper column C