Help needed on zero filling the left and middle and concatenate

[Access_Mania]

I import part numbers from a text file into a table in Microsoft Access 2003.
The field is titled PART_NUMBER and it is a text field.
The format required for each part number must be (e-mail address removed) where 0's represent numbers and @ represents an alpha character.
Unfortunately the text file eliminates any zeros in the part number.

I need to be able to:
Left fill the PART_NUMBER up to 3 characters BEFORE the alpha character.
Left fill the PART_NUMBER up to 5 characters AFTER the alpha character.
Concatenate any characters AFTER 9 characters once it is zero filled.


For example, my table contains the following text in the PART_NUMBER field:
123R4567R01
2R632
1R2
43K88M01
32K4R01


I need the field to look like this:
123R04567
002R00632
001R00002
043K00088
032K00004


I am able to Left zero fill the field using a format command, but then I am stumped on how to zero fill AFTER the alpha character and be able to concatenate the last three characters if necessary.

Is this even possible in microsoft access?? I need to either update the table for the required format or at least be able to pull the required format in a query.

Thank you in advance for your help.