Changing first letter in a text cell string globally in row 1 with same the letter range (A to D, then also F to I)

[Bertha]

I have a spreadsheet with names of theaters in row 2 and letter/digitcodes in row 1. The letter/digitcodes always start with one capital letter from the range A-P. The letter/digitcodes can have more than one letter and more than one digit, but only one capital letter, and only at the beginning of the theatercode:

C3: Ac2
D3: Da34
E3: B34
F3: E342

C4: Theater des Westens
D4: Kaaitheater
E4: National Theatre
F4: Théâtre de l'Odéon

I am looking for a formula that will change the data as follows:

If one of the capital letters changes, all the others should change as well with a same range. Example: If a capital letter D is changed into and E, anywhere in row 1, any capital A in row 1 should become a capital B, a G should become an H, etc. (but small letters should remain unchanged. If B is changed into D (a distance of two letters in the alfabet, then all capital letters should be changed with the same range of two, so an F should become an H, a C should become an E.

Is this be possible in Excel?

I am not experienced in Excel.

Thank you,
Bertha

 

[Bertha]

I have a spreadsheet with names of theaters in row 2 and letter/digitcodes in row 1. The letter/digitcodes always start with one capital letter from the range A-P. The letter/digitcodes can have more than one letter and more than one digit, but only one capital letter, and only at the beginning of the theatercode:

C3: Ac2
D3: Da34
E3: B34
F3: E342

C4: Theater des Westens
D4: Kaaitheater
E4: National Theatre
F4: Théâtre de l'Odéon

I am looking for a formula that will change the data as follows:

If one of the capital letters changes, all the others should change as well with a same range. Example: If a capital letter D is changed into and E, anywhere in row 1, any capital A in row 1 should become a capital B, a G should become an H, etc. (but small letters should remain unchanged. If B is changed into D (a distance of two letters in the alfabet, then all capital letters should be changed with the same range of two, so an F should become an H, a C should become an E.

Is this be possible in Excel?

I am not experienced in Excel.

Thank you,
Bertha

-