# 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
- Decode a Base64 value to string - Excel formula
- 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
- Insert random characters based on key
- Get 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:

### 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)

becomes

LEN("Defend the castle")

and 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)

becomes

SEQUENCE(17, , 17, -1)

and returns

{17; 16; 15; 14; 13; 12; 11; 10; 9; 8; 7; 6; 5; 4; 3; 2; 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)

becomes

MID("Defend the castle", {17; 16; 15; 14; 13; 12; 11; 10; 9; 8; 7; 6; 5; 4; 3; 2; 1}, 1)

and returns

{"e"; "l"; "t"; "s"; "a"; "c"; " "; "e"; "h"; "t"; " "; "d"; "n"; "e"; "f"; "e"; "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))

becomes

TEXTJOIN(, TRUE, {"e"; "l"; "t"; "s"; "a"; "c"; " "; "e"; "h"; "t"; " "; "d"; "n"; "e"; "f"; "e"; "D"})

and returns

"eltsac eht dnefeD".

## 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)

becomes

LEN("DEFENDCASTLE")

and 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)

becomes

RANDARRAY(36, , 65, 90, TRUE)

and returns

{65; 89; 87; 82; 90; 83; 87; 77; 77; 82; 85; 76; 86; 80; 71; 86; 67; 71; 85; 81; 78; 66; 75; 81; 68; 87; 66; 80; 78; 83; 81; 66; 67; 86; 66; 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))

becomes

CHAR({65; 89; 87; 82; 90; 83; 87; 77; 77; 82; 85; 76; 86; 80; 71; 86; 67; 71; 85; 81; 78; 66; 75; 81; 68; 87; 66; 80; 78; 83; 81; 66; 67; 86; 66; 89})

and returns

{"A"; "Y"; "W"; "R"; "Z"; "S"; "W"; "M"; "M"; "R"; "U"; "L"; "V"; "P"; "G"; "V"; "C"; "G"; "U"; "Q"; "N"; "B"; "K"; "Q"; "D"; "W"; "B"; "P"; "N"; "S"; "Q"; "B"; "C"; "V"; "B"; "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)))

becomes

TEXTJOIN(, TRUE, {"A"; "Y"; "W"; "R"; "Z"; "S"; "W"; "M"; "M"; "R"; "U"; "L"; "V"; "P"; "G"; "V"; "C"; "G"; "U"; "Q"; "N"; "B"; "K"; "Q"; "D"; "W"; "B"; "P"; "N"; "S"; "Q"; "B"; "C"; "V"; "B"; "Y"})

and 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)

becomes

SEQUENCE(12, , 1, 3)

and returns

{1; 4; 7; 10; 13; 16; 19; 22; 25; 28; 31; 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)

becomes

MID("AYWRZSWMMRULVPGVCGUQNBKQDWBPNSQBCVBY", {1; 4; 7; 10; 13; 16; 19; 22; 25; 28; 31; 34}, 3)

and returns

{"AYW"; "RZS"; "WMM"; "RUL"; "VPG"; "VCG"; "UQN"; "BKQ"; "DWB"; "PNS"; "QBC"; "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)

becomes

{"D";"E";"F";"E";"N";"D";"C";"A";"S";"T";"L";"E"}&{"AYW"; "RZS"; "WMM"; "RUL"; "VPG"; "VCG"; "UQN"; "BKQ"; "DWB"; "PNS"; "QBC"; "VBY"}

and returns

{"DAYW";"ERZS";"FWMM";"ERUL";"NVPG";"DVCG";"CUQN";"ABKQ";"SDWB";"TPNS";"LQBC";"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))

becomes

TEXTJOIN(, FALSE, {"DAYW";"ERZS";"FWMM";"ERUL";"NVPG";"DVCG";"CUQN";"ABKQ";"SDWB";"TPNS";"LQBC";"EVBY"})

and 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)))

becomes

UPPER("DAYWERZSFWMMERULNVPGDVCGCUQNABKQSDWBTPNSLQBCEVBY")

and returns

"DAYWERZSFWMMERULNVPGDVCGCUQNABKQSDWBTPNSLQBCEVBY"

## 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.

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

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

becomes

LEN("Hello, World!")

and 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))

becomes

SEQUENCE(13)

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 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)

becomes

MID("Hello, World!", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 1)

and 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))

becomes

CODE({"H"; "e"; "l"; "l"; "o"; ","; " "; "W"; "o"; "r"; "l"; "d"; "!"})

and returns

{72; 101; 108; 108; 111; 44; 32; 87; 111; 114; 108; 100; 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)

becomes

DEC2BIN({72; 101; 108; 108; 111; 44; 32; 87; 111; 114; 108; 100; 33},8)

and returns

{"01001000"; "01100101"; "01101100"; "01101100"; "01101111"; "00101100"; "00100000"; "01010111"; "01101111"; "01110010"; "01101100"; "01100100"; "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))

becomes

TEXTJOIN(, TRUE, {"01001000"; "01100101"; "01101100"; "01101100"; "01101111"; "00101100"; "00100000"; "01010111"; "01101111"; "01110010"; "01101100"; "01100100"; "00100001"})

and returns

"01001000011001010110110001101100011011110010110000100000010101110110111101110010011011000110010000100001"

#### 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

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

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)))

## 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:

## 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))) < 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

## 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 are links to detailed explanations:

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)

becomes

LEN("DEFENDTHECASTLE")

and 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))

becomes

SEQUENCE(,15)

and returns

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

#### Step 3 - Split characters in cell C2

Function syntax: MID(text, start_num, num_chars)

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

becomes

MID("DEFENDTHECASTLE",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)

and 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

becomes

FIND({"D","E","F","E","N","D","T","H","E","C","A","S","T","L","E"},"ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1

becomes

{4,5,6,5,14,4,20,8,5,3,1,19,20,12,5}-1

and returns

{3,4,5,4,13,3,19,7,4,2,0,18,19,11,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))

becomes

MOD({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}, 10)

and returns

{0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}

#### Step 6 - Split characters in code key

Function syntax: MID(text, start_num, num_chars)

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

becomes

MID("KINGARTHUR",{0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}+1,1)

becomes

MID("KINGARTHUR",{1,2,3,4,5,6,7,8,9,10,1,2,3,4,5},1)

and 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)

becomes

FIND({"K","I","N","G","A","R","T","H","U","R","K","I","N","G","A"}, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")

and returns

{11,9,14,7,1,18,20,8,21,18,11,9,14,7,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

becomes

{3,4,5,4,13,3,19,7,4,2,0,18,19,11,4}{11,9,14,7,1,18,20,8,21,18,11,9,14,7,1}

and returns

{14,13,19,11,14,21,39,15,25,20,11,27,33,18,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))

becomes

MOD({14,13,19,11,14,21,39,15,25,20,11,27,33,18,5}, 10)

and returns

{14,13,19,11,14,21,13,15,25,20,11,1,7,18,5}

#### Step 10 - Get characters

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)

becomes

MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", {14,13,19,11,14,21,13,15,25,20,11,1,7,18,5}, 1)

and returns

{"N","M","S","K","N","U","M","O","Y","T","K","A","G","R","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))

becomes

TEXTJOIN(, TRUE, {"N","M","S","K","N","U","M","O","Y","T","K","A","G","R","E"})

and returns

"NMSKNUMOYTKAGRE".

## 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:

## 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)

becomes

LEN("FINDSEAMOTH")

and 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)

becomes

LEN("FINDSEAMOTH")/LEN("CAD")

becomes

11/3

and 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)

becomes

ROUNDUP(3.6666666667,0)

and 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))

becomes

REPT("CAD",4)

and 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))

becomes

LEFT("CADCADCADCAD",LEN("FINDSEAMOTH"))

becomes

LEFT("CADCADCADCAD",11)

and 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 -

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:

### Excel categories

### 14 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

Hi I tried on google sheets both you caesar Cypher solutions (number 8 and number 11), but I am getting only the first letter of the text that I am writing (when I try that in excel I only get an error, so that's an improvement...)

Any idea about the possible cause of that?

Giovanni

The TEXTJOIN function is only available to Excel 2019 users or later versions.

Check the cell references in the formulas.

Did you enter the formulas as array formulas?

This is great content! How would I extend the Base64 encoding to handle non-alphanumeric characters? For example, If I start with

`Hello, World!`

, the formula gives me`SGVsbG9YgV29ybGRB`

, but it should be`SGVsbG8sIFdvcmxkIQ`

. If I remove the comma, space and exclamation, it works as expected.BS,

thank you!

I think I figured it out, however, the last character is a "B" and not a "Q"?

BS,

I got the padding working with the last 6-digit block. The last character is now "Q".

It looks like the Base64 spec pads the end of the binary source string with zeros to make sure the final bit count is a multiple of both 6 and 8. The exclamation point plus padded zeros is

`00100001 00000000 00000000`

. When split into 6 bit segments for Base64, that makes the final 3 bytes into 4 Base64 characters:`I Q = =`

.I think your formula would either have to append binary zeros until the bit count is a multiple of 24, or append null characters to the source string until its length was a multiple of 3.

That works! Very impressive, sir.

Thank you.

I have added a new section: How to decode a base64 value.

Hello,

I figured out a pseudo manual way to perform the Vigenere cipher in Apple Numbers, but then wanted to apply your Excel formula to test it out. However, Numbers doesn't use a "sequence" function, so how could I alter your formula to test it in Apple Numbers? I found somewhere that I could use a function combining transpose >row>indirect however, since you sequence the len, I don't quite understand how to change this within your function (I'm needing the C8 function where I'm decoding the message):

=TEXTJOIN(,1,MID(B10,MOD(FIND(MID(C6, SEQUENCE(LEN(C6)), 1), B10)-FIND(MID(C7, MOD(SEQUENCE(LEN(C6))-1, LEN(C7))+1, 1), B10),LEN(B10))+1,1))

JL,

I don't know if this works in Numbers:

Replace every instance of SEQUENCE(LEN(C6))-1

with

ROW(A1:INDEX(A:A,LEN(C6)))-1