Using Microsoft Excel to encrypt and decrypt messages

[Kford-academy]

Here is a complicated (!) set of instructions that I have made to encrypt and decrypt caesar shift ciphers. With this, you can change the alphabet and there is even a checking system in there to ensure that any characters that are not present in the alphabet but are present in the plaintext/ciphertext do not result in an error message. Here it is…

1. Enter your alphabet in A1, your key (a number) in B1 and your plaintext (encryption) or ciphertext (decryption) in C1
2. Enter ‘=MID($A$1,ROW(),1)’ in D1 and copy vertically (down) for as many cells as there are characters in the alphabet
3. Enter ‘=IF(ISERROR(INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1))=TRUE,MID($C$1,ROW()-1,1),INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1))’ (encyption) or ‘=IF(ISERROR(INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)-$B$1-1,LEN($A$1))+1,1))=TRUE,MID($C$1,ROW()-1,1),INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)-$B$1-1,LEN($A$1))+1,1))’ (decyption) in A2 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
4. Enter ‘=A2’ in B2
5. Enter ‘=CONCATENATE(B2,A3)’ in B3 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
6. You should have your message in the last cell of column B

I have tried this and it does actually work! You may need to research the equivalents of the functions used if you don’t use Excel.

If you still have any doubts about whether it works, try decrypting ‘b tjnqmf nfttbhf’ with a key of 1, and alphabet ‘abcdefghijklmnopqrstuvwxyz’ (although without the apostrophes/quotation marks). You should get ‘a simple message’.

There is one downside to this, and that is that (at least for my 2000 version of Excel) you can’t have double letters in the alphabet (e.g. ‘a’ and ‘A’). But I am working on it!

[Kford-academy]

Here is my improved version of my previous post. THIS VERSION IS-CASE SENSITIVE.

1. Enter your alphabet in A1, your key (a number) in B1 and your plaintext (encryption) or ciphertext (decryption) in C1
2. Enter ‘=IF(ISERROR(MID($A$1,MOD(FIND(MID($C$1,ROW()-1,1),$A$1,1)+$B$1-1,LEN($A$1))+1,1))=TRUE,MID($C$1,ROW()-1,1),MID($A$1,MOD(FIND(MID($C$1,ROW()-1,1),$A$1,1)+$B$1-1,LEN($A$1))+1,1))’ (encryption) or ‘=IF(ISERROR(MID($A$1,MOD(FIND(MID($C$1,ROW()-1,1),$A$1,1)-$B$1-1,LEN($A$1))+1,1))=TRUE,MID($C$1,ROW()-1,1),MID($A$1,MOD(FIND(MID($C$1,ROW()-1,1),$A$1,1)-$B$1-1,LEN($A$1))+1,1))’ (decryption) in A2 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
3. Enter ‘=A2’ in B2
4. Enter ‘=CONCATENATE(B2,A3)’ in B3 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
5. You should have your message in the last cell of column B

[Mattyrat2027]

@Kford-academy try as I might it tells me that there is a syntax error – for an if statement between 2 and 3 statments are needed but only 1 was given. Is it my software (Numbers) or am I doing something completely wrong? I trust that your code works, so I assume the problem is at my end.

[F6exb]

Playing OTP with Excel, retrieving the key with plaintext and ciphertext:
1) Split texts in columns, one letter by cell. A few weeks ago, I wrote :

In Excel or Calc:
• Copy and paste the first column from the text editor into A1 in the spreadsheet. (You can write 32767 characters in a cell).
• In A10, enter 1.
• In A11, enter 2.
• Select the 2 cells A10, A11, extend vertically (down) for as many cells as there are characters in the plaintext/ciphertext.
• In B10 write the formula: “=MID($A$1,A10,1)” and copy down.
• Select the column , copy, and do a special paste with values only, on the right, for example D10

So, you copy side by side the plaintext and the ciphertext. For example Plaintext in column from D10 and ciphertext in E10.

2) I don’t like very long formulas so I use a lot of columns. It is more easy to understand.
In F10 and G10 I calculate ranks of each letter of each text in a clear alphabet:
• In F10 : “=CODE(D10)-64”
• In G10 : “=CODE(E10)-64”
• In H10 : “=G10-F10” ===> Difference between plain letter and cipher letter. Sometimes it is > 0 and sometimes it is <0.
• In I10 : “=MOD(H10+1,26)” ===> Rank in alphabet of the key letter.
• In J10 : “=CHAR(I10+64)” ===> The key letter.

3)
• Select D10 to J10

and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext

© KFA, thank you for the english lesson ;-). You read the key in column J.

• If you have the key and the plaintext / ciphertext, you can encrypt / decrypt by changing D E H column.

[Kford-academy]

@Mattyrat2027, I apologise for the complicated functions and expressions used! I have done a thorough explanation here of the first of my methods, so I hope this helps. It definitely works for me, so I think this is a problem on your end. (That’s the problem with complicated functions!)

VERSION 1
[Note: For clarity, we shall use the encryption formula when we get to Step 3.]

Function 1 (Step 2) – ‘=MID($A$1,ROW(),1)’
The MID function gets you a string of text from a larger string of text. This particular function gives you the nth letter of the alphabet used, where n is the row number of the cell (so you want the 1st letter in D1, the 2nd letter in D2, the 3rd letter in D3, and so on).

Function 2 (Step 3) – ‘=IF(ISERROR(INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1))=TRUE,MID($C$1,ROW()-1,1),INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1))’
This includes the IF function, which is composed of three parts. The first part is a true/false checker (see Function 3). The second part is the value of the cell if the first part is true (see Function 7). The third part is the value of the cell if the first part is false (see Function 4).

Function 3 (Step 3) – ‘=ISERROR(INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1))’
The ISERROR function is a checker to see if a function or expression is an error or not. The function this is checking is Function 4, which is the value of the cell this is in if this is not an error.

Function 4 (Step 3) – ‘=INDEX(D:D,MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1,1)’
The INDEX function is used to locate a cell in a range of cells. The first part is the range of cells – column D. The second part is the row number (see Function 5). The third part is the column number – which is always 1 as there is only 1 column!

Function 5 (Step 3) – ‘=MOD(MATCH(MID($C$1,ROW()-1,1),D:D,0)+$B$1-1,LEN($A$1))+1’
This includes the maths function MOD (short for modulo), which gives you the remainder after dividing a number by another number. For instance, 13/5 = 2 remainder 3, so 13 (mod 5) is 3. The first part of the MOD function is the number you start with – so 13 in the previous example (see Function 6). [Note: The ‘-1’ (inside the MOD function) is there with the ‘+1’ (outside the MOD function), purely so any numbers which would have come out with 0 would instead come out with a number that would make sense with Function 4. (Remember, this is the row number of the range of cells in Function 4.) The ‘+$B$1’ is used to shift a letter so many places forward in the alphabet, which is the whole purpose of the caesar shift cipher – everything else in the instructions is formatting!] The second part of the MOD function is the divisor – so 5 in the previous example (see Function 8).

Function 6 (Step 3) – ‘=MATCH(MID($C$1,ROW()-1,1),D:D,0)’
Note that the ‘+$B$1-1’ has been taken out, as this is mainly used as a diversion within the MOD function (see Function 5).
The MATCH function is usually used for seeing which cells are the same as a piece of text, although this can be used for other purposes. The first part of the MATCH function is the text which we wish to find (see Function 7). The second part of the MATCH function is the range of cells in which we want to find is the range of cells in which we wish to find the text (column D) and the third part of the MATCH function indicates that we want to get the position of the first cell that is EXACTLY THE SAME as the text we wish to find. Note that this number may vary depending on what spreadsheet program/application you are using.

Function 7 (Step 3) – ‘=MID($C$1,ROW()-1,1)’
Again, the MID function gets you a string of text from a larger string of text. This gets you the (n-1)th letter of the ciphertext/plaintext, where n is the row number of the cell (so you want the 1st letter in A2, the 2nd letter in A3, the 3rd letter in A4, and so on).

Function 8 (Step 3) – ‘=LEN($A$1)’
The LEN function (short for length) gives the length of a piece of text. This gives us the length of the alphabet used.

Function 9 (Step 5) – ‘=CONCATENATE(B2,A3)’
The CONCATENATE function joins together two or more pieces of text. B2 was the first letter of the final message, and A3 was the second letter – so B3 is the first two letters of the final message. As this function is copied vertically, in B4 we get =CONCATENATE(B3,A4). This joins together the first two letters of the final message (B3) and the third letter (A4), so B4 is the first three letters of the final message. This continues to get you your whole message.

[Mattyrat2027]

@F6exb thanks for this, I am no longer getting syntax errors!

Please could you just specify which cells all the functions go in for #53054? I also got a bit lost with the (step 2) and (step 3) etc.

Thanks

[F6exb]

@Mattyrat2027
For steps 1, 2, 3 ask KFA 😉
He speaks about Caesar cipher and I speak about OTP.

[Kford-academy]

@Mattyrat2027 About post #53064, I am happy to help wherever I can! (At least it gives me something to do…) This is the clarification:

VERSION 1
Step 1 – The alphabet goes in A1. The key goes in B1. The ciphertext/plaintext goes in C1.
Step 2 – The function given should go in D1 and be copied vertically.
Step 3 – The functions given should go in A2, depending on whether you want to encrypt or decrypt the message, and should be copied vertically.
Step 4 – The function given should go in B2.
Step 5 – The function given should go in B3 and be copied vertically.

VERSION 2
Step 1 – The alphabet goes in A1. The key goes in B1. The ciphertext/plaintext goes in C1.
Step 2 – The functions given should go in A2, depending on whether you want to encrypt or decrypt the message, and should be copied vertically.
Step 3 – The function given should go in B2.
Step 4 – The function given should go in B3 and be copied vertically.

[Mattyrat2027]

@F6exb Hehe I wasn’t thinking straight 😉

I meant @Kford-academy!

[Kford-academy]

Here are simpler versions of the caesar shift encryptors/decryptors that I put earlier. The only change is that a lot more columns are used. However, this makes it easier to debug if required.

Simpler version of version 1:
1. Enter your alphabet in A1, your key (a number) in B1 and your plaintext (encryption) or ciphertext (decryption) in C1
2. Enter ‘=MID($A$1,ROW(),1)’ in D1 and copy vertically (down) for as many cells as there are characters in the alphabet
3. Enter ‘=MID($C$1,ROW(),1)’ in E1and copy vertically (down) for as many cells as there are characters in the ciphertext/plaintext
4. Enter ‘=MATCH(E1,D:D,0)’ in F1 and copy vertically (down) for as many cells as there are characters in the ciphertext/plaintext
5. Enter ‘=MOD(F1+$B$1-1,LEN($A$1))+1’ (encryption) or ‘=MOD(F1-$B$1-1,LEN($A$1))+1’ (decryption) in G1 and copy vertically (down) for as many cells as there are characters in the ciphertext/plaintext
6. Enter ‘=INDEX(D:D,G1,1)’ in H1 and copy vertically (down) for as many cells as there are characters in the ciphertext/plaintext
7. Enter ‘=IF(ISERROR(H1)=TRUE,E1,H1)’ in I1 and copy vertically (down) for as many cells as there are characters in the ciphertext/plaintext
8. Enter ‘=I1’ in J1
9. Enter ‘=CONCATENATE(J1,I2)’ in J2 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
10. You should have your message in the last cell of column J

Simpler version of version 2:
1. Enter your alphabet in A1, your key (a number) in B1 and your plaintext (encryption) or ciphertext (decryption) in C1
2. Enter ‘=MID($C$1,ROW(),1)’ in D1 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
3. Enter ‘=FIND(D1,$A$1,1)’ in E1 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
4. Enter ‘=MOD(E1+$B$1-1,LEN($A$1))+1’ (encryption) or ‘=MOD(E1-$B$1-1,LEN($A$1))+1’ (decryption) in F1 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
5. Enter ‘=MID($A$1,F1,1)’ in G1 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
6. Enter ‘=IF(ISERROR(G1)=TRUE,D1,G1)’ in H1 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
7. Enter ‘=H1’ in I1
8. Enter ‘=CONCATENATE(I1,H2)’ in I2 and copy vertically (down) for as many cells as there are characters in the plaintext/ciphertext
9. You should have your message in the last cell of column I

[Mattyrat2027]

@Kford-academy and @F6exb thanks for your help, it is all working like a charm, and I have a decrypt, encrypt caesar cipher spreadsheet, and one to extract the key for an OTP.

I tried to do the case sensitive version but it didn’t really work. I have managed to retain the case, but towards the end the letters and case aren’t decrypting correctly. Any help? Alphabet: ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefhijklmnopqrstuvwxyz.

[Kford-academy]

@Mattyrat2027, firstly, you are missing a lower case g from your alphabet. Once you have added that in, try the following decryption:

[Simpler version of version 2]
1. Enter ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz’ in A1, ‘1’ in B1 and ‘B Rvjdl Fybnqmf gps Nbuuzsbu2027’ in C1.
2. Enter ‘=MID($C$1,ROW(),1)’ in D1 and copy vertically (down). You should get ‘B’ in D1, a space in D2, ‘R’ in D3, ‘v’ in D4, and so on.
3. Enter ‘=FIND(D1,$A$1,1)’ in E1 and copy vertically (down). You should get ‘2’ in E1, an error message in E2, ’18’ in E3, ’48’ in E4, and so on.
4. Enter ‘=MOD(E1-$B$1-1,LEN($A$1))+1’ in F1 and copy vertically (down). You should get ‘1’ in F1, an error message in F2, ’17’ in F3, ’47’ in F4, and so on.
5. Enter ‘=MID($A$1,F1,1)’ in G1 and copy vertically (down). You should get ‘A’ in G1, an error message in G2, ‘Q’ in G3, ‘u’ in G4, and so on.
6. Enter ‘=IF(ISERROR(G1)=TRUE,D1,G1)’ in H1 and copy vertically (down). You should get ‘A’ in H1, a space in H2, ‘Q’ in H3, ‘u’ in H4, and so on.
7. Enter ‘=H1’ in I1. You should get ‘A’ in I1.
8. Enter ‘=CONCATENATE(I1,H2)’ in I2 and copy vertically (down). You should get ‘A ‘ in I2, ‘A Q’ in I3, ‘A Qu’ in I4, ‘A Qui’ in I5, and so on. In I32, you should get the following plaintext: ‘A Quick Example for Mattyrat2027’.

[Mattyrat2027]

@Kford-academy thanks, I have retained the case, and the all the letters decrypt perfectly. I also adapted the code for encryption, I don’t know what I would have done without your help!

@F6exb would it be possible to create a spreadsheet or code that could extract the key from just 2 ciphertexts encrypted with the same key?

[F6exb]

@Matyrat2027:
It is possible because this is #53003 from Madness or unit 106 of his textbook 😉
I can’t solve it for the moment. I think that we must use crib dragging.

[Mattyrat2027]

@F6exb I have been researching it, and I think that C1 XOR C2 = M1 XOR M2. Could we use this to our advantage?

C1=Ciphertext 1
C2=Ciphertext 2
M1=Message 1
M2=Message 2

[Author]

Posts