New
#1
Hello @Night AoX,
Have a look at this > Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
I hope this helps.
There is a Microsoft Excel forum at: Excel Forum
Perhaps someone there might have a solution or workaround.
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.