# Worksheet on Character Formulae in Excel - Caesar's by mbw16088

VIEWS: 18 PAGES: 3

• pg 1
```									                      Worksheet on Character Formulae in Excel - Caesar's Cypher

In this worksheet we are going to duplicate a code used by Julius Caesar more than 2000 years ago. He took
plain Latin and moved each letter of the alphabet 2 steps along (so A became C, B became D, C became E etc.
Y became A, and Z became B). We can do this in Excel using character formulae and a lot of Fill Down!

1. Firstly, power up Excel. Now that you have a blank sheet in front of you, put the message that you want
to encode in cell A1, for example WE ATTACK THE GAULS AT DAWN. Stick to capital letters
without any punctuation - although you can use spaces. The spreadsheet that we are going to create will
be able to cope with spaces, but we won't unnecessarily complicate the issue by getting it to filter out
punctuation or have to deal with lower case letters. Leave cell A2 blank - that's where the coded
message will appear!
2. The first thing we have to do is to split that message up
into its individual letters. We do this by using the MID()
function to extract substrings which are only 1 letter long.
Put the number 1 in cell A4, 2 in A5, 3 in A6 etc. You may
find it easier to put the first few numbers in and then use
Fill Down to put the other numbers in. You should go
down as far as about A30, or at least until you reach the
same number as the number of letters in the message (so
for a message of 27 letters, such as the one above, you
should go down to cell A31, which will contain the
number 27).
3. In cell B4, put the following formula:
=MID(A\$1,A4,1)
What this means is "Extract the single character from the text in cell A1 at the position specified by cell
A4". Cell A4 contains the number 1, so this will extract the single character at position number 1 (W in
my example).
Please note: There is a dollar sign in the middle of the reference to A1 as we are about to fill the formula
down the column and we don't want that reference to change. If we didn't put the dollar sign in, then as
we filled down the column, the reference to A1 would change to A2, then to A3 etc. which is not what
we want. We want all the letters to be extracted from A1.

4. Fill the formula in cell B4 down the column so that it
reaches the same row as the numbers that you put in
column A. If you examine the formulae, you will find
that the reference to A4 has changed to A5, A6 etc. but
the reference to A\$1 has stayed the same (because of the
dollar). This means that the formula will extract the
second character from your message in cell B5, the third
character in B6 etc. Spaces count as characters, so some
of the cells will appear blank (even though they have
spaces in them).

5. Put the word Offset in cell A3 and the number 2 in cell B3. This will be the number that we are going to
add to the code of each letter to shift it along the alphabet. The number 2 will turn A into C, B into D
etc.
6. In cell C4 put the following formula:
=IF(B4=" ",32,CODE(B4)+B\$3)
This translates into English as "Look at the character in B4. If it is a space, put the number 32 in this
cell. Otherwise, use the code number for that character with the specified offset added on".
 The "code" values referred to are the ANSI (or ASCII, if you prefer) code values for the letters.
Letter "A" is represented in Excel (indeed in any computer program on your machine) as the
number 65, "B" as 66 etc.
   We treat spaces as special. We don't want those translated into other symbols or letters. The
reason we use the number 32 whenever we find a space is that that is the code value for space
itself, i.e. we are replacing a space with another space.
   By now, you will have guessed that we are going to fill the formula down the column. The dollar
sign prevents the reference to B\$3 from changing.

7. Now fill this formula down the C column as far as
the other formulae were filled. You should end up
with something like this. If you have filled the
columns down further than the length of the
message (i.e. there are some "spare" slots at the
end), then you will find that the formula produces
an error in those spare slots. This is because the
formula is trying to calculate the CODE value of a
blank cell (which doesn't have one). Don't let this
bother you. We can just ignore those cells at the
end. You may want to encode a longer message in
future, in which case they will be needed.

8. It is possible that adding the offset to the codes of the letters may push some of them beyond Z in the
alphabet, in which case they will be coded by punctuation symbols. We need to implement a "wrap-
around" system where Y translates to A and Z translates to B.
With this in mind, put the following formula in cell D4
=IF(C4 > CODE("Z"),C4-26,C4)
This means "If the value in C4 is beyond the code value for Z, then subtract 26 from it (which gives the
wrap-around), otherwise, just use C4 as it stands." Then fill this formula down the column.
If your message doesn't contain any Ys or Zs, then this column will simply duplicate the values in
column C. Any letter Ys or Zs, on the other hand, will be coded by the numbers 65 (A) and 66 (B).

9. Now all we need to do is turn the numbers into
letters and string them all together to form the
coded message. In cell E4 put the following
formula:
=CONCATENATE(E3,CHAR(D4))
The contents of this cell will be the contents of
cell E3 (which is blank) with the character
whose code is in D4 added on the end. Since
E3 is blank, this will just be the character
whose code is in D4.

Fill this formula down the column. You will
find that each cell will hold the contents of the
cell above it with the character whose code is
in the D column tacked on the end. The coded
message will be built up letter by letter:

10. The last step is to transfer the coded message into cell A2 for completeness. Put in cell A2 the following
formula:
=E30
This formula applies to this particular message. The final message has been built up in cell E30. Of
course, you will need to change this depending on the exact length of your message:

You can change the code simply by changing the value of the offset (in B3) to any whole number from 1 to 25.
If made the offset 0, then each letter would be the same in code and you would end up with the plain message
again.
A slight improvement

It's rather a nuisance having to change the cell reference in A2 every time you want to put a message of a
different length in code. It would be so much nicer if it could cope with them automatically.
 Change the formula in C4 into the following:
=IF(A4 > LEN(A\$1), 0, IF(B4 = " ", 32, CODE(B4)+B\$3))
What you had before was just the last part of that. This new formula translates as "If this slot is beyond
the end of the message (the LENgth of the message in A\$1), then put a 0 in this slot, otherwise check
the character as before (is it a space etc.)".
 Fill the formula in C4 down the column, overwriting the formulae that are in there from the previous
version. You won't notice any change in the first few slots, but at the end of the message you will see
slots filled with 0s.

You notice that 0s appear at the end of the the D column as well. This is because it is only designed to
stop the numbers going beyond the code value of Z. However, the E column still reports an error
because it is trying to add character 0 (which doesn't exist) on to the end of the message.
   Now we change the formula in column E so that it only adds the character whose code is in the cell on
its left if that character isn't zero. If it is zero, then it ignores that character and simply duplicates the
message in the cell above. Change the formula in E4 into the following:

=IF(D4 > 0,CONCATENATE(E3,CHAR(D4)),E3)

This simply means, "If D4 contains a proper character value (bigger than 0), then add it to the message
in the cell above (E3). Otherwise, just use the message in cell E3 unadulterated".

   Fill the formula in E4 down the column overwriting the ones that were in there. You will find that if you
fill down below the length of the message, then it stops growing and simply stays at its final length. You
can now fill all the columns A to E down to row 100 (or 200 for that matter) knowing that the E column
of the last row will contain a copy of the final coded message instead of an error. 100 rows means that
you can cope with messages of up to 100 characters. If you think you may need more, make it row 200,
or 2000.

   Finally, change the formula in A2 to
=E100

(or E200 or whatever you have chosen as your final row). This means that you can change the message
in A1 as much as you like (providing you don't go above 200 characters or whatever the magic length
is), and the coded message should appear in A2

Decoding Messages

Putting messages into code is all very well, providing you can decode them afterwards. This can be done very
easily by putting the coded message into A1 and altering the value in one single cell. What single change would
you make?

Here is a sample message for you to decode

PX ATOX UXTMXG MAX ZTNEL EXML MKR MAX KNLLBTGL GHP

and another:

V PNZR V FNJ NAQ V PBHYQAG ORYVRIR ZL RLRF

```
To top