Sort column with '000 format

[ajcrig99]

Hopefully this one is super easy, I have a column of numbers starting at 0 and going to 200 but must be 3 digits long. I have added '00 and '0 to all numbers below 100 so they display as 001, 002 etc but now they do not sort properly. This is how they show up

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]113[/TD]
[/TR]
[TR]
[TD]200[/TD]
[/TR]
[TR]
[TD]201[/TD]
[/TR]
[TR]
[TD]202[/TD]
[/TR]
[TR]
[TD]000[/TD]
[/TR]
[TR]
[TD]009[/TD]
[/TR]
[TR]
[TD]010[/TD]
[/TR]
[TR]
[TD]011[/TD]
[/TR]
[TR]
[TD]028[/TD]
[/TR]
[TR]
[TD]033[/TD]
[/TR]
</tbody>[/TABLE]

This is how I want them to show:

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]000[/TD]
[/TR]
[TR]
[TD]009[/TD]
[/TR]
[TR]
[TD]010[/TD]
[/TR]
[TR]
[TD]011[/TD]
[/TR]
[TR]
[TD]028[/TD]
[/TR]
[TR]
[TD]033[/TD]
[/TR]
[TR]
[TD]113[/TD]
[/TR]
[TR]
[TD]200[/TD]
[/TR]
[TR]
[TD]201[/TD]
[/TR]
[TR]
[TD]202[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I am guessing you have a mix of numbers and numbers entered as text (you can tell if you take off the alignment and some are right-justified (numbers), and some are left-justified (text).

In order to get them to sort properly, they must ALL be of the same data type (numeric or text).

So, if you are typing a single quote before your leading zeroes, then you must enter EVERY number like that (or change the column to Text format, and re-enter your values).
Or, enter all the values as numbers, and use a Custom format of "000" to display them as three digit numbers.

It is an "either or" situation. You cannot mix them up, and get them to behave the way you want.

 

[ajcrig99]

Thankyou, I added a ' to each cell and now it behaves as expected.

 

[Joe4]

You are weclome.

You can also avoid having to do that if you format the whole column as Text before typing in your values.