Solution - Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
Mon 19
Dec 2016
Sat 19
Nov 2016
Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.
The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.
Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)
Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.
The Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher
The answer to this challenge would be published after a month i.e. on 19-Dec-16.
Tue 08
Nov 2016
Solution - Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter
Below is a possible solution to the Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter.
Put following formula and drag down
=IFERROR(CHAR(IF(CODE(LOWER(G2))-$B$3<97,CODE(G2)-$B$3+26,CODE(G2)-$B$3)),"")
The Excel file containing the solution can be downloaded from Solution - Challenge 53 - Decryption - Caesar's Shift Cipher
Sat 08
Oct 2016
Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter
We published a cryptography challenge Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher. The purpose of the challenge was to create an Excel based Encrypter.
This time, we need to create the de-crypter for the same. The Excel file related to this challenge can be downloaded from Challenge 53 - Decryption - Caesar's Shift Cipher
Tue 27
Sep 2016
Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta
Below is a possible solution to the Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta
Put following formula and drag down and right
=CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65)
A workbook containing the above solution can be downloaded from Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta
Sat 27
Aug 2016
Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta
Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta
One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row is from A to Z and first column is from A to Z (Yellow row and column). You need to populate all other values by a single formula. (Yellow row and column is something which you can put as constant. But white area is something which you need to populate)
The solution to the above challenge will be published after a month i.e. on 27-Sep-16.
Mon 09
May 2016
Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
Below is a possible solution to the challenge - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
1. Make a backup of your workbook
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given below
6. Save your file as .xlsm
7. Call your function as =EncryptCS(A1,$B$2)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Function EncryptCS(PText, Shift) As String Dim Ws As Worksheet Dim i As Long Dim CText As String Dim PArr, CArr Application.Volatile PArr = Split(StrConv(PText, vbUnicode), vbNullChar) ReDim CArr(UBound(PArr) - 1) For i = LBound(PArr) To UBound(PArr) - 1 If Abs(77.5 - Asc(UCase(PArr(i)))) < 13 Then If Asc(LCase(PArr(i))) + Shift > 122 Then CArr(i) = Chr(Asc(PArr(i)) + Shift - 26) Else CArr(i) = Chr(Asc(PArr(i)) + Shift) End If Else CArr(i) = PArr(i) End If Next i EncryptCS = Join(CArr, "") End Function |
A workbook containing the above solution can be downloaded from Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher.
Tue 26
Apr 2016
Solution - Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher
Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher.
Put below formula in G2 and drag down -
=IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26,
CODE(D2)+$B$3)),"")
The workbook containing the solution can be downloaded from Solution - Challenge 39 - Caesar's Shift Cipher.
Sat 09
Apr 2016
Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
We made ourselves familiarized with Caesar's Shift in last challenge - Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher
That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher.
The challenge before you is to write a VBA function which takes cell or text as first argument and amount of shift as second argument to generate the Cipher Text.
Hence, to generate answer in A2, we have called the function as =EncryptCS(A1,B2)
Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.
The answer to this challenge would be published after a month i.e. on 09-May-16.
Sat 26
Mar 2016
Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher
This is time for a cryptography challenge. The simplest cipher in cryptography is Caesar's Shift. More about this can be read here (though it will not be needed for this challenge) - https://en.wikipedia.org/wiki/Caesar_cipher
For a given alphabet, it simply substitutes by another alphabet by a fixed number of position. Hence, if shift position is 5, a will be f, A will be F, M will be R, x will be c, Z will be E.
Now challenge before you is to write a formula to achieve the same. The result should be case sensitive.
Hence, you will put the Shift in cell B3 and the answer would be expected in column H for column D.
The workbook related to this challenge can be downloaded from Challenge 39 - Caesar's Shift Cipher
The answer to this puzzle will be published after a month i.e. on 26-Apr-16.