Very Large Crime Database and I need to apply proper codes to it

I have a data base with 20 columns and 30,000 rows of data. The key column is the one which has the Offense Code. The codes are both numbers and some with number and letter. And if there is a letter behind the number it could have one space or two space or three space...like this 02101 A, or 02777 Z or 30400 C. In these examples the first has three spaces and then 2 and then 1.

I tried to use VLookup to give these codes the proper index code which is shown in the orange column heading with the proper Description. However, I am not able to successfully sort by lowest to highest because of the various letters behind the numbers and also the way the numbers are arranged.

The columns highlighted in yellow shows the way the codes are being displayed after sorting from lowest to highest. The orange columns are the correct codes and descriptions but they are not in the same order as the ones in the yellow after sorting. The green columns represent the way I want the columns and rows to looks like after all is done. In some cases there will be "more' or "less" codes depending on the crime for that month. But all codes will be used during the year as each month crime report will have some or all codes.

Any suggestion would be helpful. I have multiple years of data that I will have to work through. If I can find a formula that works, I can save myself weeks of work. Thank you as always.

Here is one on many database (SHORTENED) files that I have to convert. If you use my earlier attachment and see this attachment, then you can get an idea of what I am trying to do. The yellow highlight columns are the actual database. The Green columns are what I want to have after conversion using Vlookup or a program that can automatically assign the new code and description.
Since the actual database column represents the"Code Order in the File", I have not been able to properly sort it so that the Codes Needed in Green can be placed automatically. Hope I explained good enough. Thanks

Dave

No short codes in 1 is because I left a blank cell there. They jump because order of the codes in yellow highlight represent different crimes. The 50 will index to a Burglary and the Short Code for that is a "5". There is also codes in yellow highlight like "103" that represent the crime of Murder by Firearm, but the Short Code is a "0" and the short description is "Murder".
When I sort from small to largest for the Codes in yellow highlight called "Code Order In File", this is the manner in which they sorted. Some of the codes in yellow highlight have "letters" and that prevents the numbers from sorting in the small to largest order. I hope I explain. thanks for looking into this for me

Here is the data base. The only columns needing to be vlookup are "Offense Code" and the long description from the code sheet and then the Short code and short description.

To add the proper Description using the code index highlighted in orange in my other posts and then apply the short code and short description to all.
The attached file has various columns but the only one I want to use is the one in yellow highlight. That is a part of the file. the columns with orange and
green are what I want to add using the Code Index and then the short code and short description.

Hope this clarifies.