Correctly sorting an alpha-numeric number when not all numbers have 3 digits.

I feel like this is far beneath me, but here goes. I'm no wizard, but this is really frustrating me.

I have a column of alpha-numeric numbers - like so:

E109
E11
E11
E110
E119
E12
E12
E120
E120
E121
E121
M137
M138
M139
M139
M14
M140

I am trying to sort them like this:

E11
E11
E12
E12
E109
E110
E119
E120
E120
E121
E121
M14
M137
M138
M138
M139
M139
M140
M141
M142

I know it sorts right to left when a numeric value, and I don't know how to do it the other way.

I have tried formatting the numbers as a custom format "000" I have tried changing the format to numbers, text, etc..... I have also tried text to columns, then making the numbers 3 digits, then pasting the values, and combining them. I've finally tried =TEXT(a1,"xxxx")

Can anyone please help me with this situation? I am sure it's something super easy, or there is a way to split the column and format, etc.

Thank you,

Christianpax_mfst_all.xlsx

I'm going to try to figure out exactly what you did there, but thank you my friend. You are a saint!!!!!!!!!!!!!

After you put the helper columns in, which column do you click on to sort a-z?

I tried a few different columns, and every time it sent me back to the old results.

Thanks!

Christian

Nevermind. It's column F correct. Man I'm feeling extra green today

Hey Sktneer,

When I got home from work, I went back in to fiddle around a little more, and when I tried to recreate the intended results I couldn't get it to work anymore? Also, when I fiddled around with the one you uploaded, I also found out that it was making mistakes. Is there something I am missing?

Thanks!


pax_mfst_all my attempt.xlsx

Ha! Nevermind, I got it! I've never used the custom sort function! Thanks for dealing with me! ha! So excited!!!