Sorting alphanumeric smallest value to largest

[steventhibault]

Untitled Spreadsheet
I have 20,000+ rows of data that I need to sort based on Alphanumeric value. Sorting largest to smallest does not produce the outcome I need. See example in red. I need it to sort MT-12, MT-32, MT-63, etc... Is there a macro or easy way to do this without a large nested formula and multiple steps?

Product Series	MC-330a	288
Product Series	MC-400a	289
Product Series	MINI-LINE	298
Product Series	MT-12	299
Product Series	MT-150	300
Product Series	MT-225	301
Product Series	MT-32	302
Product Series	MT-400	303
Product Series	MT-63	304
Product Series	MT-800	305
Product Series	MT-95	306
Product Series	NAB	307
Product Series	ODE-3	308

 

[awoohaw]

you need to put a leading zero in front of your 2 digit, and two leading zeros in front of the 3 digit numbers.
you can do that with SUBSTITUTE("ABC-1","-", "-00") (I think that is the correct syntax, but I hope you get the idea, I'll post better in a minute.)

 

[awoohaw]

Yes, the above syntax is correct. But, this formula does not take into consideration the records with an "a" at the end or the one with non numerals in the string:

Book1
	A	B	C	D
4	Product Series	MT-12	299	MT-012
Sheet4

Cell Formulas
Range	Formula
D4	D4	=SUBSTITUTE(B4,"-","-0")