Sorting numbers and strings together, where strings are numbers that can be followed by letters-CodePudding

I need to sort an Excel sheet which has a mixture of actual numbers and text containing numbers possibly followed by text. For example if the following list was randomised, the end result should be:

54 <-- Actual number
55
56
57
57B <-- Number followed by a letter
57M <-- Yet another letter
58
58M
59
59M
60
60B
61
61M
62
62B
63
64
65
66
90
99
99A
100
100B
100B2
120
120B

I tried a million different ways. However, I can't work out a way to sort the sheet like this.

Is there a way to do this? Some sort of "humanised" sorting?

CodePudding user response：

Add a helper column with the following formula:

=TEXT(IFERROR(LEFT(A1,MIN(IF(ISERROR(--MID(A1,SEQUENCE(LEN(A1)),1)),SEQUENCE(LEN(A1))))-1),A1),"000")&IFERROR(MID(A1,MIN(IF(ISERROR(--MID(A1,SEQUENCE(LEN(A1)),1)),SEQUENCE(LEN(A1)))),LEN(A1)),"")

Then sort on that.

Note: Change the "000" to the same number of digits as the largest number in the list.

Also if one does not have SEQUENCE substitute that part with ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))) The formula would then also need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Sorted on Column A:

Sorted on Column B: