Using Microsoft Excel to encrypt and decrypt messages

[Mattyrat2027]

@The-letter-wriggler yes, it worked for your ciphertext. However, it came out wrong for a different plaintext. I encrypted TESTPLAINTEXTFORVIGENERECIPHER and got (when using a website, and key: ENIGMA) XRAZBLEVVZQXXSWXHIKRVKDEGVXNQR. When I encrypted TESTPLAINTEXTFORVIGENERECIPHER with the spreadsheet, I got PRKNDLWVFNSXPSGLJICRFYFEYVHBSR, which happens to be the same as decrypting TESTPLAINTEXTFORVIGENERECIPHER with the key, not encrypting. What is even more interesting is that decryption of the official ciphertext, XRAZBLEVVZQXXSWXHIKRVKDEGVXNQR, with the key = plaintext.

You, sir, have made a decryption spreadsheet!

[Kford-academy]

I have absolutely no idea if the previous vigenère cipher spreadsheet actually works or not, but here is my attempt at a more compact vigenère encryptor/decryptor:

1. Enter your alphabet in A1, your key in B1 and your ciphertext/plaintext in C1
2. Enter ‘=MID($C$1,ROW(),1)’ in D1 and copy vertically (down) for the length of the ciphertext/plaintext
3. Enter ‘=IF(ISERROR(FIND(D1,$A$1,1))=TRUE,0,1)’ in E1
4. Enter ‘=IF(ISERROR(FIND(D2,$A$1,1))=TRUE,E1,E1+1)’ in E2 and copy vertically (down) for the length of the ciphertext/plaintext
5. Enter ‘=MID($B$1,MOD(E1-1,LEN($B$1))+1,1)’ in F1 and copy vertically (down) for the length of the ciphertext/plaintext
6. Enter ‘=FIND(F1,$A$1,1)-1’ in G1 and copy vertically (down) for the length of the ciphertext/plaintext
7. Enter ‘=FIND(MID($C$1,ROW(),1),$A$1,1)+G1’ (encryption) or ‘=FIND(MID($C$1,ROW(),1),$A$1,1)-G1’ (decryption) in H1 and copy vertically (down) for the length of the ciphertext/plaintext
8. Enter ‘=MOD(H1-1,LEN($A$1))+1’ in I1 and copy vertically (down) for the length of the ciphertext/plaintext
9. Enter ‘=MID($A$1,I1,1)’ in J1 and copy vertically (down) for the length of the ciphertext/plaintext
10. Enter ‘=IF(ISERROR(J1)=TRUE,MID($C$1,ROW(),1),J1’ in K1 and copy vertically (down) for the length of the ciphertext/plaintext
11. Enter ‘=K1’ in L1
12. Enter ‘=CONCATENATE(L1,K2)’ in L2 and copy vertically (down) for the length of the ciphertext/plaintext
13. Your ciphertext/plaintext should be in the last cell of column L

[The-letter-wriggler]

Here is a FREQUENCY AND DIGRAPH ANALYSIS spreadsheet.

Convention: CELL – what goes in it – [what to do]

A1 FREQUENCY AND DIGRAPH ANALYSIS
A2 Put Text To Analyse In A3 – it MUST contain ONLY letters
A3 [*ENTER TEXT TO ANALYSE HERE*]
A4 =LEN(A3)
B4 Text Length

P5 Digraph analysis
P6 Second letter of pair
A7 Letter
B7 Count
A8 a
A9 =CHAR(CODE(A8)+1) [select A9 and copy down to A33]
B9 =LEN(A$3)-LEN(SUBSTITUTE(LOWER(A$3),A8,””)) [select B9 and copy down to A33]

D19 First
D20 Letter
D21 Of Pair

E8 A
E9 =CHAR(CODE(E8)+1) [select E9 and copy down to E33]
F7 A
G7 =CHAR(CODE(F7)+1) [select G7 and copy across to AE7]
F8 =(LEN($A$3)-LEN(SUBSTITUTE(UPPER($A$3),$E8&F$7,””)))/2
[select F8 and copy across to AE8]
[select all F8 to AE8 and copy down to row F33]

[Use sensible column widths to make it look good]

[F6exb]

It would be interesting if this thread is saved on github with the others archives.

[It might, but on the other hand it might undermine the challenge a bit! We like people to write their own code to crack these challenges so would prefer not to make it too easy for them to download tools designed to do that! Harry]

[Mattyrat2027]

This suggests that there are other things on github. Where can I find them and what are they?

[F6exb]

Sometimes Madness speaks about this. Stay tuned.

[Mattyrat2027]

@TLW Great spreadsheet!

I had removed the brackets at each end of the formula in F8, and it went funny, counting in decimals, and I can assure you, a double Q does not appear over 9200 times!

After adding them back in, though, it is working brilliantly – thanks!

[Author]

Posts