Using Microsoft Excel to encrypt and decrypt messages

[F6exb]

C1 XOR C2 = M1 XOR M2
If we can guess a good crib we can XOR this crib with C1 XOR C2:
M2 XOR (M1 XOR M2) = M1. (or M1 XOR (M1 XOR M2) = M2)

But here it is not a true XOR.
M1 + k = C1
M2 + k = C2
C1 – C2 = M1 – M2
If we have a crib of a few letters: M1, then if this crib is at the good place in (C1 – C2) ===> (C1 – C2) + M2 = M1 and we have some letters of the 2 clear texts. But we can’t say which word is in wich message. From this crib we can also recover some letters of the key.
We have to slide the crib one letter after one letter along the (C1 – C2) and if we get something readable, it is a good place.
Is that right ?

I hope that you can undestand my “special english” 😉

[Mattyrat2027]

@F6exb That sounds good to me.

The way I have interpreted crib-drag is that you get the hex of each ciphertext, XOR them together, and then XOR the hex of your key at evy position. Take the resulting XOR and convert it to text. Then, if it is ‘readable’ text, you know that the crib and the ‘readable text’ are in the same positions across the ciphertexts. You have to expand the crib each time, and this is how I am making progress with @Madness’ challenge.

[F6exb]

In #53091 the big chief said:

@Mattyrat2027, the challenges in this thread use Vigenere-like ways of combining the key with the plaintext, not XOR.
The key for #53003 is a random string of uppercase letters.

It can’t be a true XOR with hex code because in this case, we’ll get letters outside of the alphabet:
for example “F” XOR “W”
46 ===> 0100 0110
57 ===> 0101 0111
——————–
0001 0001

“A” XOR “Z”
41 ===> 0100 0001
5A ===> 0101 1010
——————–
0001 1011

It is a kind of Vigenère and the crib uses only the rank in the alphabet.

@Madness, are you here ?

[Mattyrat2027]

Yipee! I’ve created a substitution cipher spreadsheet!

1. In A1, enter your ciphertext (You could use FAD UVEMI LHBGT OBX QVSCJ BWDH FAD RKZY NBP)
2. In B1, enter the plaintext alphabet, let’s take it to be A-Z.
3. In C1, enter =MID($B$1,ROW(),1), and drag down until the whole plaintext alphabet is revealed.
4. In D1, don’t put anything. The ciphertext alphabet will go here, but later.
5. In E1, enter =FIND(D,$B$1,1). Extend down so it is the same length as the plaintext alphabet.
6. In F1, enter =MID($A$1,ROW(),1), and extend down for the length of the ciphertext.
7. In G1, enter =FIND(F1,$B$1,1) and extend down for the length of the ciphertext.
8. In H1, enter =INDEX(D,G1,1) and extend down for the length of the ciphertext. Don’t worry about the error messages.
9. In I1, enter =IF(ISERROR(H1)=TRUE,F1,H1) and extend down for the length of the ciphertext.
10. In J1, enter =H1.
11. In J2, enter =CONCATENATE(J1,I2), and extend down to the same length as the ciphertext.
12. In D1 you put your ciphertext, one cell per letter extending down. (If you don’t want to have to solve it yourself, here it is: hopeitwrksabcdfgjlmnquvxyz) The plaintext should be ‘THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG’ 🙂

Let me know if there is anything to improve, I have made it as efficient as possible. I couldn’t find a function that did the job without having to convert to numbers. Thanks for unknowingly providing most of the functions!

[Kford-academy]

@Mattyrat2027, a couple of things. Firstly, you should enter ‘=I1’ in J1, not ‘=H1’ (otherwise you may have an error message). Secondly, you should put ‘=MID($D$1,ROW(),1)’ in E1, as this gives the ciphertext alphabet one cell at a time. Hope this helps!

[Kford-academy]

Oh, one more thing. You should also enter ‘=INDEX(D:D,G1,1)’ in H1.

[Mattyrat2027]

@Kford-academy thanks for the tips, my version did work, so maybe I got lost in transcribing it. However, I deleted the columns with numbers and used XLOOKUP instead, to cut down on a step. Knowing your ability, you probably can work out where to put the function for yourself.

I was trying to create a cipher clock spreadsheet, but I couldn’t work out how to do it cumilatively, if you get me, starting counting where you ended. Any help?

[Kford-academy]

@Mattyrat2027, thanks for the challenge. I am making good progress!

[Kford-academy]

Got it, @Mattyrat2027! Here is my method to encrypt or decrypt with the cipher clock.

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

[Mattyrat2027]

@Kford-academy Thank you so much for your help. It works!

I am thinking now about a potential Vigenère spreadsheet. It would either have to break the ciphertext down into columns (transcribing across) or it would have to take every Nth letter and work from there, if my thinking is correct. Any ideas?

[Kford-academy]

@Mattyrat2027, I believe you are correct. One formula I suggest is ‘=MID([key],MOD(ROW(),LEN([key])),1)’.

[The-letter-wriggler]

In general I do not use spreadsheets for my ciphers (unless there is a special need) but as you seem to be having a good time at making them up here is my offering.

I do not use Excel I use the spreadsheet in the free Open Office Suite.
It uses semi-colons (;) in place of commer (,) in the functions.
I have changed all occurences so hopefully it will work in Excel.

To keep things simple I give the cell name first then what goes in it [bracket notes].

A1 VIGENERE ENCRYPTION [simply the title]
A2 Plain [see C2]
A3 Clean [see C3 this cleans – replaces the symbols given with nothing here space , ! and .]
A4 Cipher
A7 Keyword

C2 HELLO WORLD [Enter the PLAINTEXT in C2, example HELLO WORLD given]
C3 =UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,” “,””),”,”,””),”!”,””),”.”,””))
C4 =C620 [or the last cell number that you dragged the C40 column down to]
C5 4 [Enter the length of the keyword as a NUMBER, this example keyword will be LIFE]
C6 A [Enter a capital A]
D6 =CHAR(CODE(C6)+1) [Select D6 and drag to cell AB6]
C7 =IF(B7<>””,CHAR(MOD(CODE(C$6)-CODE(UPPER($B7)),26)+CODE(“A”)),””)[Select C7 and drag to AB7]
Select all cells C7 to AB7 and drag down to row 32 [enough for a 26 letter keyword]

A40 1 [enter 1]
B40 =HLOOKUP(RIGHT(LEFT($C$3,A40),1),C$6:AF$13,2+MOD(A40-1,C$5),FALSE())
C40 =B40
A42 =A40+1
Select B40 and C40 and drag down one row to B41 – C41
Select A41,B41,C41 and drag down to row 620 [remember C620 is put in C4]

B7-B10 L I F E [Enter the KEYWORD at B7,B8,B9,B10…, ciphertext will appear at C4]

With example given you should get WWGHDOJNAV

You are on your own to make it decrypt.

[The-letter-wriggler]

Sorry, here is a correction – the AF$13 should be AB$32 like so…

B40 =HLOOKUP(RIGHT(LEFT($C$3;A40);1);C$6:AB$32;2+MOD(A40-1;C$5);FALSE())

It references the (last cell) bottom right cell of the matrix.
If you ever extend it the 32 will need updating.

[Mattyrat2027]

@The-letter-wriggler I had to alter it slightly – do you mean ‘ A41 =A40+1 ‘ ?
This worked for me when I tried it. Also, I take it some functions reference cells to be filled in later, e.g. ‘ C4 =C620 ‘. In this case, are the rows extended so far to accomodate a longer ciphertext?

[The-letter-wriggler]

@Mattyrat2027
Yes it should have been A41 =A40+1 and not A42 as you know its for copy dragging the numbers down.
I merely went down to C620, yes it is for longer ciphertext. C4 is just a convenience for viewing the ciphertext.
C4 can be any reference to the Cn column as long as it is further than the length of the ciphertext, that is why it is best to reference the last one. If you did this nothing needs filling in later. You will notice that the matrix fills as you type the keyword.

I saw your post and made it for you, is it OK?

[Author]

Posts