Numbers Starting with 0 Stored as Text not sorting correctly in table

I really don't want to have to make a "helper" column that the only purpose is to fix the "help" that is causing the problem in the first place... I can't fathom that whoever programmed the sorting capabilities of Excel ignored the primary way we are taught how to sort EVERYWHERE, except apparently in Excel... alphanumeric sorting puts numbers first, IN ORDER, followed by letters. Can't Microsoft just take a look at any phone book and see THIS should at least be an OPTION?!? It's ridiculous that 50,000 steps need to be taken, made, modified, cheated, faked, and sorted by false data to get something that should be the DEFAULT setting, in my opinion. Every office filing system, books, heck the Dewey decimal system in the library that has been in place for over 100 years follows these rules.

Myall, that does work, which I guess is better than nothing, but certainly not optimal. I use filter drop downs so I can resort to view different aspects of data, like vendor, cost, stock quantity, etc, which still won't work on the item number, so the only way to get it back to that is to select all and perform a custom sort each and every time, which is very annoying if trying to cycle through data quickly.

I guess what I'm really wondering is if it's even possible within Excel for it to forget all it's advanced professional irrational logic, and revert to a 3rd grade level, which is, I'm pretty sure, about when I first learned how to sort things alphanumerically. I want to be able to copy a list of 5,000 new items into my spreadsheet, hit sort, and bam, done. No adding characters, no hiding fake formulas and telling it to sort by wrong data in order to get the right result. I'm beginning to think it's not possible. Logically, formatting the column to TEXT should do it, since it displays exactly what you input without modifying, but sadly it doesn't treat it as exactly what you put in. Logic, it seems, is not logical, nor is common sense common or sensible.

Try performing a custom sort. Select the option 'Sort numbers and numbers stored as text separately':

nope, that separates ANYTHING with a letter separately. So, 944-212 will come before 100-47B. And if I use "sort anything that looks like a number, as a number" it puts 01 in the right place, but 01B in the wrong place. I need them all sorted together, not separated.

This only works to set an exact number of digits, I have this same problem, but all I want for Excel to stop trying to "help" me and process what I give it... I have a bank of over 50,000 item numbers, some begin with zero, but the item numbers could be anywhere from 3 to 10 characters in length, they may start with numbers or letters, and I just want Excel to PROPERLY sort alphanumerically... surely there's a way. When I'm done, I want something like this:

01-332

044-699

10325

2673A

997-446K

CS0024

It should be simple, I don't know why it isn't. Formatting as text just mixes up anything with a leading '0' and tells me I improperly formatted a number as text error. Seriously, half of my issues with Excel is it trying to "fix" my "mistakes" instead of just using what I input.