Changing text in one cell based off of text in another

[seraph001]

I want to change part of the text in one cell based off of part of text in another cell.

I generate a list of data that can be from either this year or last year. This is denoted by either 20BC###### or 21BC######. We give this an additional number KC-GPGY-######. I would like to be able to in real time paste in my data (20BC or 21BC) and then be able to pull down the KC-GPGY number so that they continue on sequentially but changing the first two digits to reflect the year ie. KC-GPGY-200001 or KC-GPGY-210002.

I have attempted to create an if statement with a substitution but I couldn't make it work.

Any assistance or thoughts on how to make this work would be appreciated. I have included a small workbook with how I would like the data to look in the future.

[JohnTopley]

in A1

="KC-GPGY-" &LEFT($B1,2)&TEXT(ROWS($1:1),"0000")

[seraph001]

Thank you very much!