Excel (VBA?) - Custom Cell Formats, number and text spacing

I am working on an excel sheet that tracks report numbers. The report numbers all follow the same sequence. XX########X##, where X is a letter and # is a digit 0 thru 9. All report numbers are 2 letters followed by 8 digits, then 1 letter and then 2 more digits.

The numbers/letters are not random but identify what is contained in the report. So, it's custom to refer to them in a specific cadence which is: XX ### ##### X##. As you can see, this pattern is so much easier on the brain than the above. Like how phone numbers are all said in a specific ###-#### pattern or SS is ### ## ####.

Anyway, I would like the column that these report numbers are in to "display" with spaces. I put display in quotations because I don't want to actually have a space character in the cell. This will affect other parts of the spreadsheet downstream.

Excel has built in cell formatting options (Format Cells > Custom). I am able to use to create a spacing pattern using zero (0). E.g. if my file reports were all numbers "1234567891234" I can input a custom pattern as "00 000 00000 000" and it will "display" "12 345 67891 234". However, I cannot get this to work with text and numbers. I have tried "*", "@", "?" but no luck. I am unable to find a solution in VBA but I suspect that is the way to go. Thanks!

The cells contain text. Text formatting cannot be changed with a number format. Text will always shows as entered in the cell. If you want to see spaces, there will need to be spaces in the cell.

You don't specify where the data comes from. If it is not entered manually, but loaded from another system, you could use a formula to add the spaces in the specific places and then hide the original column.

=LEFT(A1,2)&" "&MID(A1,3,3)&" "&MID(A1,6,5)&" "&RIGHT(A1,3)

or

=REPLACE(REPLACE(REPLACE(A1,11,0," "),6,0," "),3,0," ")