# 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

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
-

Replies
2
Views
675

1,196,429
Messages
6,015,212
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back