Working with classic ciphers in Excel

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:

Explaining the formula in cell C4

Step 1 - Count characters

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(C3) returns 17.

Step 2 - Create a sequence of numbers from n to 1

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(C7), , LEN(C7), -1) returns {17; 16; 15; ... ; 1}

Step 3 - Extract part of a string

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(C7, SEQUENCE(LEN(C7), , LEN(C7), -1), 1) returns {"e"; "l"; "t"; ... ; "D"}.

Step 4 - Join values in the array

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, MID(C7, SEQUENCE(LEN(C7), , LEN(C7), -1), 1)) returns "eltsac eht dnefeD".

Back to top

2. Insert random characters

The formula in cell C4 inserts random characters between each plaintext character based on the number in cell C4. Inserting random characters at a regular interval is easily deciphered, however, much harder to decipher if it is combined with another cipher.

Formula in cell C4:

Formula in cell C8:

2.1 Explaining formula in cell C4

Step 1 - Count characters

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(C3) returns 12.

Step 2 - Multiply the number of characters by the specified number in cell C4

The asterisk character lets you multiply numbers in an Excel formula.

LEN(C3)*C4 becomes 12*3 equals 36

Step 3 - Create an array of random numbers between 65 and 90

The RANDARRAY function creates an array of random numbers

Function syntax: RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE)

returns {65; 89; 87; ... ; 89}.

Step 4 - Convert numbers to characters based on ASCII

The CHAR function converts a number to the corresponding ANSI character determined by your computers character set.

Function syntax: CHAR(text)

CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE))

returns {"A"; "Y"; "W"; ... ; "Y"}.

Step 5 - Join characters in array

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE)))

returns "AYWRZSWMMRULVPGVCGUQNBKQDWBPNSQBCVBY".

Step 6 - Create a sequence of numbers

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(C3), , 1, C4) returns {1; 4; 7; ... ; 34}.

Step 7 - Split characters in the string

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(TEXTJOIN(, TRUE, CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE))), SEQUENCE(LEN(C3), , 1, C4), C4)

returns {"AYW"; "RZS"; "WMM"; ... ; "VBY"}.

Step 8 - Append characters and strings

The ampersand character & lets you append strings in an Excel formula.

MID(C3, SEQUENCE(LEN(C3)), 1)&MID(TEXTJOIN(, TRUE, CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE))), SEQUENCE(LEN(C3), , 1, C4), C4)

returns {"DAYW";"ERZS";"FWMM"; ... ;"EVBY"}

Step 9 - Join characters and strings

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, FALSE, MID(C3, SEQUENCE(LEN(C3)), 1)&MID(TEXTJOIN(, TRUE, CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE))), SEQUENCE(LEN(C3), , 1, C4), C4))

returns "DAYWERZSFWMMERULNVPGDVCGCUQNABKQSDWBTPNSLQBCEVBY".

Step 10 - Create capital letters

The UPPER function converts a value to upper case letters.

Function syntax: UPPER(text)

UPPER(TEXTJOIN(, FALSE, MID(C3, SEQUENCE(LEN(C3)), 1)&MID(TEXTJOIN(, TRUE, CHAR(RANDARRAY(LEN(C3)*C4, , 65, 90, TRUE))), SEQUENCE(LEN(C3), , 1, C4), C4)))

returns "DAYWERZSFWMMERULNVPGDVCGCUQNABKQSDWBTPNSLQBCEVBY"

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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.

Formula in cell C3:

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

Cell B8 contains 64 characters A to Z, a to z, 0 to 9, and then + / The image below shows the characters and the corresponding numbers. Note, Base64 is not a cipher.

Explaining formula

1. The formula converts each character to its equivalent ASCII code. For example, the ASCII code for the letter "A" is 65.
2. Convert the ASCII code to 8-bit binary. For example, the binary representation of the ASCII code 65 (the letter "A") is 01000001.
3. The binary digits are concatenated and then split into 6-bit blocks.
4. The 6-bit binary is converted to decimal numbers.
5. The decimal number corresponds to the position of a character in cell B8.
6. Repeat step 2 to 5 with the remaining characters.
7. Concatenate all characters and return Base64 value.

The numbers above are not corresponding to the steps below.

Step 1 - Count characters in string

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(C2) returns 13.

Step 2 - Create a sequence from 1 to n

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(C2)) returns {1; 2; 3; ... ; 13}.

Step 3 - Split characters one by one

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(C2, SEQUENCE(LEN(C2)), 1) returns {"H"; "e"; "l"; "l"; "o"; ","; " "; "W"; "o"; "r"; "l"; "d"; "!"}.

Step 4 - Convert character to ANSI code

The CODE function returns the corresponding number for the first character based on your computers character set. (PC- ANSI)

Function syntax: CODE(text)

CODE(MID(C2, SEQUENCE(LEN(C2)), 1)) returns {72; 101; 108; ... ; 33}.

Step 5 - Convert ANSI code to binary

The DEC2BIN function converts a decimal number to a binary number.

Function syntax: DEC2BIN(number, [places])

DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8) returns {"01001000"; "01100101"; ... ; "00100001"}

Step 6 - Join binary digits

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)) returns

"0100 ... 1"

Step 7 - Count binary digits

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)) returns 104.

Step 8 - Add numbers and return a total

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))

Step 9 - Divide total by 6

SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6

Step 10 - Round the number up

The ROUNDUP function calculates a number rounded up based on the number of digits to which you want to round the number.

Function syntax: ROUNDUP(number, num_digits)

ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0)

Step 11 - Create a sequence fom 1 to n with a step of 6

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0), , , 6)

Step 12 - Split binary digits based on the sequence

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(TEXTJOIN(, TRUE, DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)), SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0), , , 6), 6)

Step 13 - Convert binary digits to decimal numbers and add 1

The BIN2DEC function converts a binary number to the decimal number system.

Function syntax: BIN2DEC(number)

BIN2DEC(MID(TEXTJOIN(, TRUE, DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)), SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0), , , 6), 6))+1

Step 14 - Get characters from cell C8

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B8, BIN2DEC(MID(TEXTJOIN(, TRUE, DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)), SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0), , , 6), 6))+1, 1)

Step 15 - Join characters

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, MID(B8, BIN2DEC(MID(TEXTJOIN(, TRUE, DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)), SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2, SEQUENCE(LEN(C2)), 1)),8)))/6, 0), , , 6), 6))+1, 1))

Step 16 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

TEXTJOIN(,1,MID(B8,BIN2DEC(MID(TEXTJOIN(,TRUE,DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8)),SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8)))/6,0),,,6),6)&REPT("0",6-LEN(MID(TEXTJOIN(,TRUE,DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8)),SEQUENCE(ROUNDUP(SUM(LEN(DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8)))/6,0),,,6),6))))+1,1))

x - DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8)

y - MID(TEXTJOIN(,1,x),SEQUENCE(ROUNDUP(SUM(LEN(x))/6,0),,,6),6)

LET(x,DEC2BIN(CODE(MID(C2,SEQUENCE(LEN(C2)),1)),8),y,MID(TEXTJOIN(,1,x),SEQUENCE(ROUNDUP(SUM(LEN(x))/6,0),,,6),6),TEXTJOIN(,1,MID(B8,BIN2DEC(y&REPT("0",6-LEN(y)))+1,1)))

Back to top

7.1 Convert Base64 to string - Excel 365 formula

This example decodes a Base64 value to a string using only an Excel formula.

Excel 365 formula in cell C6:

The Base64 value is in cell C5 and cell B8 contains A to Z, a to z, 0 to 9, and + /

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:

Back to top

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:

Back to top

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:

Back to top

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.

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))) < 91 And Asc(UCase(Mid(enc, i, 1))) > 64 Then
        If (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) > 90 Then
            res = res & Chr(65 + (Asc(UCase(Mid(enc, i, 1))) + Range("B8") - 90))
        ElseIf (Asc(UCase(Mid(enc, i, 1))) + Range("B8")) < 65 Then
            res = res & Chr(90 - (64 - (Asc(UCase(Mid(enc, i, 1))) + Range("B8"))))
        Else
            res = res & Chr(Asc(UCase(Mid(enc, i, 1))) + Range("B8"))
        End If
    Else
        res = res & 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))) < 91 And Asc(UCase(Mid(dec, i, 1))) > 64 Then
        If (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) > 90 Then
            res = res & Chr(65 + (Asc(UCase(Mid(dec, i, 1))) - Range("B8") - 91))
        ElseIf (Asc(UCase(Mid(dec, i, 1))) - Range("B8")) < 65 Then
            res = res & Chr(90 - (64 - (Asc(UCase(Mid(dec, i, 1))) - Range("B8"))))
        Else
            res = res & Chr(Asc(UCase(Mid(dec, i, 1))) - Range("B8"))
        End If
    Else
        res = res & Mid(dec, i, 1)
    End If
Next i

Range("B2") = res

End Sub

Get excel *.xlsm file

Encrypt message.xlsm

Back to top

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:

Back to top

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:

Back to top

14. Double transposition cipher

Back to top

15. Vigenere cipher

The Vigenere cipher uses the same key repeatedly across the entire message, here are links to detailed explanations:

• Vigenere cipher
• What is 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:

Explaining formula in cell C4

Step 1 - Count characters

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(C2) returns 15.

Step 2 - Create an array of numbers from 1 to n horizontally

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(,LEN(C2)) returns {1,2,3,... ,15}

Step 3 - Split characters in cell C2

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(C2,SEQUENCE(,LEN(C2)),1) returns {"D","E","F","E","N","D","T","H","E","C","A","S","T","L","E"}

Step 4 - Calculate position in alphabet for plain text

The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

Function syntax: FIND(find_text,within_text, [start_num])

FIND(MID(C2,SEQUENCE(,LEN(C2)),1),$B$10)-1 returns {3,4,5,... ,4}

Step 5 - Create a repeating sequence

The MOD function returns the remainder after a number is divided by divisor.

Function syntax: MOD(number, divisor)

MOD(SEQUENCE(,LEN(C2))-1, LEN(C3)) returns {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}

Step 6 - Split characters in code key

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(C3,MOD(SEQUENCE(,LEN(C2))-1,LEN(C3))+1,1) returns {"K","I","N","G","A","R","T","H","U","R","K","I","N","G","A"}

Step 7 - Find relative position in alphabet for each character in code key

The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

Function syntax: FIND(find_text,within_text, [start_num])

FIND(MID(C3, MOD(SEQUENCE(,LEN(C2))-1, LEN(C3))+1, 1), B10) returns {11,9,14,... ,1}

Step 8 - Add arrays and subtract 1

The plus and minus signs let you add and subtract numbers respectively in an Excel formula.

FIND(MID(C2, SEQUENCE(,LEN(C2)), 1), B10)-1+FIND(MID(C3, MOD(SEQUENCE(,LEN(C2))-1

returns {14,13, ... ,5}

Step 9 - Calculate the remainder

The MOD function returns the remainder after a number is divided by divisor.

Function syntax: MOD(number, divisor)

MOD(FIND(MID(C2,SEQUENCE(,LEN(C2)),1),$B$10)-1+FIND(MID(C3,MOD(SEQUENCE(,LEN(C2))-1,LEN(C3))+1,1),$B$10),LEN(B10))

returns {14,13,19,... ,5}

Step 10 - Get characters

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B10, MOD(FIND(MID(C2, SEQUENCE(LEN(C2)), 1), B10)-1+FIND(MID(C3, MOD(SEQUENCE(LEN(C2))-1, LEN(C3))+1, 1), B10), LEN(B10))+1, 1) returns {"N","M","S",...,"E"}

Step 11 - Join letters

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, MID(B10, MOD(FIND(MID(C2, SEQUENCE(LEN(C2)), 1), B10)-1+FIND(MID(C3, MOD(SEQUENCE(LEN(C2))-1, LEN(C3))+1, 1), B10), LEN(B10))+1, 1))

returns "NMSKNUMOYTKAGRE".

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

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:

Back to top

27. Bifid cipher

Formula in cell C3:

Formula in cell C6:

Back to top

28. Trifid

Back to top

29. Frequency analysis

Formula in cell C6:

Formula in cell D6:

Back to top

30. Tabula recta

Formula in cell C5:

Back to top

31. Insert random characters based on a key - LAMBDA function

This formula pads a text string with random letters based on a a given key. The key specifies how many random letters are between each letter in the text string.

Excel 365 formula in cell C4:

Explaining formula

The key "CAD" specifies the number of random characters between each letter in the text string. "C" is the third letter in the alphabet, three random letters are attached to the first letter in the text string. In this example letters "SNL" to "F".

The second letter in the key is "A", it is the first letter in the alphabet, one random letter is needed between the first and second letters in the text string. "SNLF" and "R" becomes "SNLFR".

The third letter in the key is "D", it is the fourth letter in the alphabet. The formula appends four random letters between the second and third letters in the text string. The key is repeated until all characters are padded with random letters.

Step 1 - Count characters in the given text string

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(C2) returns 11.

Step 2 - Divide character lengths for text and key strings

The division sign lets you divide numbers in an Excel formula.

LEN(C2)/LEN(C3) returns 3.6666666667

Step 3 - Round the number up to its nearest integer

The ROUNDUP function calculates a number rounded up based on the number of digits to which you want to round the number.

Function syntax: ROUNDUP(number, num_digits)

ROUNDUP(LEN(C2)/LEN(C3),0) returns 4.

Step 4 - Repeat key string

The REPT function repeats a specific text a chosen number of times.

Function syntax: REPT(text,number_times)

REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)) returns "CADCADCADCAD"

Step 7 - Extract n characters from left

The LEFT function extracts a specific number of characters always starting from the left.

Function syntax: LEFT(text, [num_chars])

LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)) returns "CADCADCADCA".

Step 8 -

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(C2))

Step 9 -

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1)

Step 10 -

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

Function syntax: SEARCH(find_text,within_text, [start_num])

SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Step 11 -

The RANDARRAY function creates an array of random numbers

Function syntax: RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(,b,65,90,1)

Step 12 -

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(a,RANDARRAY(,b,65,90,1))

Step 13 -

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1)))

Step 14 -

The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.

Function syntax: REDUCE([initial_value], array, lambda(accumulator, value))

REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1))))

Step 15 -

The CHAR function converts a number to the corresponding ANSI character determined by your computers character set.

Function syntax: CHAR(text)

CHAR(REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1)))))

Step 16 -

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(CHAR(REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1))))),1)

Step 17 -

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR(DROP(CHAR(REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1))))),1),"")

Step 18 -

MID(C2,SEQUENCE(LEN(C2)),1)

Step 19 -

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(IFERROR(DROP(CHAR(REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1))))),1),""),MID(C2,SEQUENCE(LEN(C2)),1))

Step 20 -

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("",1,HSTACK(IFERROR(DROP(CHAR(REDUCE("",SEARCH(MID(LEFT(REPT(C3,ROUNDUP(LEN(C2)/LEN(C3),0)),LEN(C2)),SEQUENCE(LEN(C2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),LAMBDA(a,b,VSTACK(a,RANDARRAY(,b,65,90,1))))),1),""),MID(C2,SEQUENCE(LEN(C2)),1)))

Excel 365 formula in cell C8:

Back to top

Get the Excel file

Working-with-ciphers.xlsx

Excel categories

Comments (14)