I would approach this a little differently to (hopefully) simplify things a little!
Let's assume you will use Rows 4, 7, 10, etc to enter the text of your words/sentences (which is the approach in your screenshot).
Let's also assume that you're only going to use Columns C to W to enter your text into (although there's no reason why it can't be more columns, but this is just what I gather from your screenshot).
For this answer, let's assume your Alphabet is in Row 50 and your Substituted letters are in Row 51.
In summary, this means:
- the formula you need will have to be entered in every cell from C5 to W5, C8 to W8, C11 to W11, and so on
- your alphabet will be in Row 50
- your substituted letters will be in Row 51.
So, let's build this using the following steps:
- In Row 50 add the letters of the alphabet from A50 to Z50
- In Row 51 add your substituted letters
- Enter the following formula in cell of C5:
(The reason we're using
IFERROR in the formula is so Numbers will ignore any spaces in your sentences, rather than displaying an ugly error icon.)
- Copy and paste the formula from cell C5 to your other cells (i.e. cells D5:W5, C8:W8, C11:W11, and so on)
- Now start entering your text to be ciphered into cells C4:W4, C7:W7, C10:W10 and so on
- As you enter the text, the ciphered result should appear in the cell below. If it's a space, the cell below will remain blank
- Be careful to only include the letter and nothing else in each cell of your Alphabet row. Having a space or anything else will cause problems. For example, if you entered your
E with a space after it, then Numbers will never find the E because it's looking for an 'E' and not a 'E ', if you get what I mean.
- If you have the need to have no substitute value for some letters of the alphabet, then the result will be a 0 (i.e. a zero is displayed) instead of a blank cell. If this applies to you, you could create a conditional highlight to hide the result.
- Personally, I would actually put your alphabet and substituted values in another worksheet, but this may not be necessary for your purposes.