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.