## Working with classic ciphers in Excel

### What's on this page

- Reverse text
- Insert random characters
- Convert letters to numbers
- How to shuffle characters in the alphabet
- Convert string to HEX
- Convert string to ASCII
- Convert string to Base64
- Ceasar cipher
- Atbash cipher
- Trithemius cipher
- Substitution cipher
- Transposition cipher
- Column transposition cipher
- Double transposition cipher
- Vigenere cipher
- Running key cipher
- One time pad cipher
- Autokey cipher
- Homophonic substitution cipher (1)
- Homophonic substitution cipher (2)
- XOR
- Four-square
- Straddling checkerboard
- ADFGVX cipher
- Polybius square
- Nihilist cipher
- Bifid
- Trifid
- Frequency analysis
- Tabula recta
- Download Excel *.xlsx file

Most if not all formulas presented here use functions that are only available in Excel 365. Some but perhaps not all formulas can be converted so they can be used in older Excel versions. The TEXTJOIN function requires a VBA user defined function which you can find here: How to use the TEXTJOIN function

Each section contains a formula that encrypts plain text messages and a formula that decrypts a cipher. Often the encrypted text is used to decrypt it back to plain text to make sure that the decryption formula works fine.

## 1. Reverse text

The formula in cell C4 changes the text entered in cell C3, the last character is first and the first character is last, and so on. This simple step can make it harder for a dictionary attack to recognize words.

Formula in cell C4:

Formula in cell C8:

## 2. Insert random characters

The formula in cell C4 inserts random characters between each plaintext character based on the number in cell C4.

Formula in cell C4:

Formula in cell C8:

## 3. Convert letters to numbers

The formula in cell D4 converts each letter in cell D3 to a number representing the relative position in the alphabet displayed in cell C9. You can easily randomize the alphabet to make the encryption even harder.

There is a formula in the next section that demonstrates how to pseudo-randomize characters in a given text string.

This is a simple substitution cipher and can easily be decrypted using frequency analysis.

Formula in cell C4:

Formula in cell C8:

## 4. How to shuffle characters in the alphabet

The formula in cell B6 changes the order of each character in cell B3. Press F9 to recalculate.

Formula in cell B6:

## 5. Convert string to HEX

The formula in cell C3 extracts each character, converts them to their ASCII code, and then calculates the corresponding hexadecimal value.

Formula in cell C3:

Formula in cell C6:

## 6. Convert string to ASCII

The image above demonstrates a formula that converst letters to their corresponding ASCII code number. This makes it a simple substitution cipher.

Formula in cell C3:

Formula in cell C6:

## 7. Convert string to Base64

The formula in cell C3 converts a string entered in cell C2 and returns base64, it works for strings longer than or equal to 2 characters. Note, Base64 is not a cipher.

Formula in cell C3:

The equal sign is sometimes used as padding, however, the formula above does not append the character automatically.

## 8. Ceasar cipher

The Caesar cipher is a simple substitution cipher that rotates the alphabet based on the number in cell C3. A becomes n and so on. Read more: Caeser cipher

The English alphabet has 26 letters, if you use 13 as a key you can use the same calculation to both encrypt and decrypt. ROT13 is a Caesar cipher with 13 as a key. The formulas shown here works for all key numbers.

You can change the alphabet used by editing the string in cell B11. For example, use only uppercase letters and the alphabet becomes ABCDEFGHIJKLMNOPQRSTUVWXYZ.

Formula in cell C4:

Formula in cell C9:

## 9. Atbash cipher

The Atbash cipher is a simple substitution cipher that maps each character to its reverse. The first letter becomes the last and the second letter becomes the second last letter and so on.

The example shown in the image above uses both upper and lower letters including numbers and some other characters. Change the value in cell B8 to ABCDEFGIJKLMNOPQRSTUVWXYZ if you want an easier alphabet.

Formula in cell C3:

Formula in cell C6:

## 10. Trithemius cipher

Trithemius cipher moves one character by each step. This makes it much harder to crack using frequency analysis.

For example, ABC becomes BDF. A tabula recta shown below makes it easier to encrypt and decrypt strings manually using the Trithemius cipher, however, the formulas below makes it even easier. Simply type the plain text message in cell C2 and the formula in cell C3 encrypts it for you based on the alphabet provided in cell B8.

Formula in cell C3:

Formula in cell C6:

## 11. Substitution cipher

The substitution cipher maps each character to a different character. Use frequency analysis to break the substitution cipher here is a frequency analysis formula.

The example above allows you to use whatever alphabet you want and map it to whatever character you want. I am using both upper and lower letters, as well as, numbers and some other often used characters.

Formula in cell C3:

Formula in cell C6:

A twitter account belonging to NSA posted this weird message one morning in the beginning of May.

*Want to know what it takes to work at NSA? Check back each Monday in May as we explore careers essential to protecting our nation*"

A famous substitution cipher is the Caesar cipher, rotating each letter a number of places.

According to wikipedia, the cipher was reasonably secure at the time because Caesar's enemies would have been illiterate. :-)

The following macro rotates each letter in cell B2 by a number found in cell B8.

VBA Code

Sub Encrypt() Dim enc As String Dim res As String enc = Range("B2") For i = 1 To Len(enc) If Asc(UCase(Mid(enc, i, 1))) &amp;amp;amp;amp;amp;amp;amp;amp;amp;lt; 91 And Asc(UCase(Mid(enc, i, 1))) &amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 64 Then If (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) &amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 90 Then res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(65 + (Asc(UCase(Mid(enc, i, 1))) + Range("B8") - 90)) ElseIf (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) &amp;amp;amp;amp;amp;amp;amp;amp;amp;lt; 65 Then res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(90 - (64 - (Asc(UCase(Mid(enc, i, 1))) + Range("B8")))) Else res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(Asc(UCase(Mid(enc, i, 1))) + Range("B8")) End If Else res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Mid(enc, i, 1) End If Next i Range("B5") = res End Sub Sub Decrypt() Dim dec As String Dim res As String dec = Range("B5") For i = 1 To Len(dec) If Asc(UCase(Mid(dec, i, 1))) &amp;amp;amp;amp;amp;amp;amp;amp;amp;lt; 91 And Asc(UCase(Mid(dec, i, 1))) &amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 64 Then If (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) &amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 90 Then res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(65 + (Asc(UCase(Mid(dec, i, 1))) - Range("B8") - 91)) ElseIf (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) &amp;amp;amp;amp;amp;amp;amp;amp;amp;lt; 65 Then res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(90 - (64 - (Asc(UCase(Mid(dec, i, 1))) - Range("B8")))) Else res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Chr(Asc(UCase(Mid(dec, i, 1))) - Range("B8")) End If Else res = res &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; Mid(dec, i, 1) End If Next i Range("B2") = res End Sub

### Download excel *.xlsm file

## 12. Transposition cipher

The transposition cipher deploys characters in a grid based on the number of columns entered in cell C3.

Concatenate characters starting from the upper left corner moving down, then continue with the next column, and so on. The formula below adds a | character to show if the last character is the space character.

The transposition cipher can be broken using frequency analysis and anagramming.

Formula in cell C4:

The formula in cell C9 removes the last | character automatically. Change LEN(C7)-1 to Len(C7) to remove that functionality.

Formula in cell C9:

## 13. Column transposition cipher

The column transposition cipher uses a key to rearrange the columns in a given order based on the positions of the key characters in the alphabet.

Formula in cell C4:

Formula in cell C8:

## 14. Double transposition cipher

## 15. Vigenere cipher

The Vigenere cipher uses the same key repeatedly across the entire message, here is a detailed explanation: Vigenere cipher

The formulas finds the relative position of each character of both plain text message and the code key in the alphabet, provided in cell B10.

They then add the numbers, see image above. The sum is then the position of a different character in the alphabet.

Sometimes the sum is larger than the number of characters in the provided alphabet. The formulas calculate the remainder and use that number to extract a character.

You can change the alphabet in cell B10 to only upper letters if you want a more read-friendly code. Also, replace the FIND function with the SEARCH function in the formulas below or make sure that you only use upper letters in cell C2.

The formula in cell C4 returns an error if a character is not found in cell B10.

Formula in cell C4:

Formula in cell C8:

## 16. Running key cipher

The running key cipher is a Vigenere cipher with a longer key from a book. The formulas below and above are the same, only the key is different.

The running key cipher is explained here: Running key

Formula in cell C4:

Formula in cell C8:

## 17. One-Time Pad cipher

The One-Time Pad cipher works just like the Vigenere cipher, however, the key must be random and not pseudo-random. It is not possible to crack a One-Time Pad cipher in theory, as long as the keys are only used once hence the name One-Time.

The One-Time Pad cipher is explained here: One-Time Pad

Formula in cell C4:

Formula in cell C7:

## 18. Autokey cipher

The Autokey cipher is a Vigenere cipher, however, it uses the plaintext message concatenated with the real key. I have not yet figured out how to build a formula that can decrypt an Autokey cipher.

The LAMBDA function seems interesting, you can create a recursive function which seems handy in this case. It is still only available for Office Insiders.

Formula in cell C4:

Formula in cell C4:

## 19. Homophonic substitution cipher (1)

The homophonic substitution cipher maps each character to a different character just like the simple substitution cipher, however, some characters are mapped to multiple characters which makes the cipher harder to break using frequency analysis.

I am using the following table to map characters, the formula picks a character randomly if more than one is entered.

Formula in cell B3:

Formula in cell B7:

## 20. Homophonic substitution cipher (2)

This example is also a homophonic substitution cipher, however, a much larger table is used. It contains three unique digits per cell.

Formula in cell B3:

Formula in cell B7:

## 21. XOR

The formula in cell C3 converts each character to ASCII code and then performs bitwise XOR between the code key and the ASCII code.

Formula in cell C3:

Formula in cell C7:

## 22. Four-square cipher

The four-square cipher is a classic cipher from the 19 century that uses four grids to encrypt and decrypt messages. Two different code keys are used to change two of the grids.

The two first characters in the plain text are "a" and "m", find character "a" in the upper left grid, and find the character "m" in the lower right grid. They form a rectangle across all four grids, the two other characters in the opposite corners of the rectangle are the encrypted characters.

Note that the first code key is in the upper-right grid and the second code key is in the lower-left grid. The formulas below calculate the corresponding characters based on the plain text entered in cell C2.

Formula in cell C5:

Formula in cell C10:

## 23. Straddling checkerboard

The Straddling checkerboard converts letters to digits based on the table shown in the image below.

The table contains numbers distributed above and to the left of a secret alphabet. For example, character "s" is in column 9 and the first row, the first row has no name (blank). The encrypted number for character "s" is 9.

Character "u" is in column 3 and row 6 the encrypted numbers for character "u" is therefore 63.

The straddling checkerboard is broken with frequency analysis.

Formula in cell C5:

Formula in cell C10:

## 24. ADFGVX cipher

The ADFGVX cipher converts each character to two different characters based on a grid, see image below.

A secret alphabet is used, see image above. The formulas below use the text string in cell F2, change this text string to create your own secret alphabet. Note that character "J" is not in the grid, if you have one in your plain text replace that character with I.

Formula in cell C3:

Replace array below {"A", "B", "C", "D", "E";"F", "G", "H", "IJ", "K";"L", "M", "N", "O", "P";"Q", "R", "S", "T", "U";"V", "W", "X", "Y", "Z"} with your secret alphabet. The order is important to successfully encrypt and decrypt messages.

Formula in cell C10:

## 25. Polybius square

The Polybius square encrypts a message by placing each character in a grid. The encrypted text is the column and row number of each character concatenated to a long string.

Formula in cell C3:

Formula in cell C10:

## 26. Nihilist cipher

Formula in cell C3:

Formula in cell C10:

## 27. Bifid cipher

Formula in cell C3:

Formula in cell C6:

## 28. Trifid

## 29. Frequency analysis

Formula in cell C6:

Formula in cell D6:

## 30. Tabula recta

Formula in cell C5:

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

How to use the TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Concatenate unique distinct values

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]

### 4 Responses to “Working with classic ciphers in Excel”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi Oscar! Interesting cipher example, I have changed your code using

mod function, which simplifies it a bit.

The mod-function can also be used in formulas.

Torstein,

thanks for your comment.

Your macro rotates the letter Z to ], it should be C, if I use the value 3.

Sorry, one ) lost in pasting, I suppose. The res = - line should be:

Hi,

in Line 8 it must be ...Range("B8") - 91), like in Line 27.

BR

Mr.Hood