Sorting Alphanumeric Numbers

You could create two new columns and get the text into one and the numbers into the other one and then sort those two columns how you want and then hide the columns.

It looks like there might not be a standard naming/numbering convention so you might not be able to use regular text extraction functions, but if I'm wrong, try using the LEFT() and RIGHT() functions.

Otherwise, here are just a couple of the results on this topic from a search on TeachExcel (search bar at the top of every page):

Simple Excel Function to Extract a Word or Text from a Cell

Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF

Some search results lead to pages with downloadable files that show how to do things, but I haven't got the videos linked-up to all of them yet.

In principle, there are two ways of sorting, numeric and alphabetical. In numeric sorting 12 will follow 2 while the alphabetical sequence has "12" before the "2". Windows Explorer features a hybrid of these where leading numbers in alphanumeric file names are sorted numeric and only the remaining portions of file strings alphabetical. By this method "2xyz" will come before "12xyz" but "12xyz12" will come before "12xyz2".

Some of these problems can be overcome by inserting zeroes into an alphabetical sort. "xyz02" will sort before "xyz12" because 0 will sort before 1. However, in this kind of solution you are already deep into ASCII sorting which is naturally different from numeric sorting. The ASCII code for 0 = 48 and ASC("1") = 49. Therefore "0" sorts before "1" and there is no cure for that.

In practise, if you need to sort two parts of a string by different methods, you need to create two parts of the string, manipulate each part so that it sorts as you need it, then join the strings and sort on the joined strings. If you are good at VBA you can do that behind the scenes, incolumns that are specially created for this purpose and removed when done. If you [have to] prefer a simpler solution you would opt for hidden columns to control the sorting.

Either way, you should start by clarifying your rules. I think it's also important to understand the limitations of sorting before creating the rules - a point at which I sense you might be stuck - which is why I explained the technical difference between numeric and ASCII sorting. To recpature: each character, including numeric ones, has an ASCII code. Alphabetical or ASCII sorting dissects the string into character code numbers and sorts by their value.