Microsoft Excel - How Do I Sort Such A List?

How Do I Sort Such A List So Things Like 10, 11, etc. Are In The Correct Place?

Hello @Night AoX,

I always found that unfortunately, when I used to use Excel, it does sort like this, and is a real pain. I used to overcome it by using 2-Digit numbers, as I found this to be easier to organize in such cases.

Paul Black said:

Hello @Night AoX,

I always found that unfortunately, when I used to use Excel, it does sort like this, and is a real pain. I used to overcome it by using 2-Digit numbers, as I found this to be easier to organize in such cases.

I recall there being a way to do it years ago..

Hello @Night AoX,

Have a look at this > Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)

I hope this helps.

Paul Black said:

Hello @Night AoX,

Have a look at this > Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)

I hope this helps.

Haha. I actually came across that already. Unfortunately, it does not have the solution I'm looking for..

retexan599 said:

There is a Microsoft Excel forum at: Excel Forum
Perhaps someone there might have a solution or workaround.

Yes, I'll definitely have to look around there. Thank you very much.

Hello @Night AoX,

This is a Forum I used to use a LOT, there are MANY excellent people there > MrExcel - Excel Questions

I hope this helps.

Like Paul, I always use the same number of digits in any list so I can use the field to sort by.


You could always add an extra column and
1 Extract the number from that text & convert it back to a number so you can sort on that new column, or
2 Extract the number from that text, determine its length [1 or 2] then use that result to re-write the column of interest with consistent two-digit numbering.
But both of these solutions are only worthwhile if the number of entries in your column of interest is so large that the effort of manually editing the original entries makes it impracticable.


Best of luck,
Denis

The problem is sorting is done on a lexicographic basis not a numerical basis i.e, character by character from left to right

Blank, 0,1,2,3....9, A,B,C......Z


See:

BA1, AB1, AB10, AB2, AB11, AC02, AD02, ACF, ACE

It will start with 1st character and decide A comes before B.

It then looks at all items that start with A and moves to next character, and decides B comes before C.

Then it looks at 3rd character and decides 1 comes before 2.

Then it looks at fourth character - if there is no fourth character, it is padded with a space (which comes before 1)

It then loops around in similar way

AB1
AB10
AB11
AB2
AC02
ACE
ACF
AD02
BA01

Basically, it is sorts on single characters at a time.

The only way round it is to pad numbers by adding leading zeros.

Same list padded

BA1, AB01, AB10, AB02, AB11, AC02, AD02, ACF, ACE

AB01
AB02
AB10
AB11
AC02
ACE
ACF
AD02
BA01

If you have > 99 numbers, you need to pad with two leading zeros, >999 - 3 leading zeros etc.

It is easy enough to modify list in Excel with a bit of character manipulation logic. @Try3 suggest a couple of way, and I have done similar (variations on @Try3's theme).

But as he says, it is probably easier to manually edit names.