Sort Data in ascending order from an Alphanumeric column

Hello everyone-
this is my first post on this website, so I apologize for the mistakes I am sure I will make.

Essentially I have what I thought was a very simple problem that I have spent over a week on now. I have a large data set that I need to sort by ascending alphanumeric order. My data would look something like this:
1E
2F
1C
4D
etc. I need it to look like this:
1A
1B
1C
2A
2B
2C
etc.
What I am looking for is an excel macro to do this in VBA. I have automated quite a bit of other things to get the data to this point and would like to add to it so the VBA will then sort all of it by this order. Currently my workaround has been this:
I make two new columns, one with this formula:
=IF(LEN(A4)=2,"0"&LEFT(A4,1),LEFT(A4,2))
It grabs the digit, converts it to a number and if it is single digit puts a 0 in front.
I then in the other column put
=Right(A4,1) which grabs the letter (it is always the right most character)
I can then sort the sheet by selecting sort, then sort and selecting column B then C to sort all the data in a multi level approach.

What I would like is a simple vba macro to automate this so I can just sort the sheet by the alphanumeric data in the one column. I have tried so many different resources and have not found a simple workaround for this. Any help would be wonderful. Thank you!

Hello and welcome to the forum,

Here's a suggestion for you :

The macro store the data in a array (for faster processing), then add a temporary column to that array to create the list of values in the ##x format (adding 0 if required)
Then we sorted on the last column and output the result.
In the example, the data is output to a second sheet that must exist first.

(The Sort2DVert sub has been in my toolkit for years now... I don't remember where I found it but it's quite handy to sort arrays)

Please Login or Register  to view this content.