Need help creating a complicated formula with letters and numbers - EXPERT REQ'D

Hello people smarter than me,

I have been trying to create a formula that will save me DAYS of messing around at work.

What I am trying to acheive is to have a sequence of numbers as follows:

BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.

This needs to be repeated for all letters to BNA01I09.

Then this sequence needs to be repeated to BNA12.

The last thing is for me to be able to change the formula in order to impliment the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09

A really tough one for me, hopefully an easy one for someone else.


Would really appreciate any help.

Thanks, Hungri.

Attach sample file

This is a manually typed version of BNA01. (Attached)
So I need this up to BNA12;
and then from BNA to BNL
Forum BNA01.xls