If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
In the past you had to use a helper column to convert the codes to something like A001 to enable the required sort order.
We can avoid a helper column with the SORTBY function.
In the image below the table is listed in the standard sort order Excel uses.
I have used a standard SORT function in cell D3 to confirm the standard sort order.
The SORTBY function allows you sort by a column that isn’t in the sort results.
In our case we will manipulate the codes to convert them into a code that will sort in the required sequence.
The formula in cell G3 has the SORTBY function.
=LET(c,A3:A11,SORTBY(A3:B11,LEFT(c)&TEXT(RIGHT(c,LEN(c)-1),"000")))The LET function captures the codes in a variable called c.
The second argument in the SORTBY function then manipulates the codes using other functions.
The LEFT function captures the first character of the code.
The RIGHT function captures the numbers.
The TEXT function converts the numbers from 1 to 001 and 10 to 010.
The & symbol joins the text and converted numbers together. This amended code structure will then be sorted correctly.
The code structure will determine the formula you need use to convert the codes.
More complex codes may require the REGEXEXTRACT function.
This would also work…
=SORTBY(A3:B11,0+MID(A3:A11,2,99))
And if you wanted to generalize this formula so you only had to supply the entire range once…
=LET(rng,A3:B11,SORTBY(rng,0+MID(TAKE(rng,,1),2,99)))
Thanks again.
Thanks Rick. This assumes the first letter is the same – my solution handles different first letters.