Alphanumeric Sort in Excel

• 2 mins read

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.