Did you know you can use Google Sheets to create your own ciphers? With the formula I’m about to share, you can easily encrypt your text into ciphered messages and even decode them later. Let me show you how to create your own ciphers in Google Sheets using a combination of functions.

Functions Used in the Cipher Formula

I’ve combined multiple functions into a single formula to achieve this ciphering method. Here’s a brief explanation of their roles:

1. ARRAYFORMULA:
   Processes all the characters in the input text dynamically without requiring individual calculations for each character.
2. CONCATENATE:
   Joins the ciphered characters into a single string to produce the final output.
3. REGEXREPLACE and SPLIT:
   These functions split the text in A2, or any cell you choose, into individual characters, placing each character into a separate cell.
4. HSTACK:
   Creates two arrays:
   • One array serves as the lookup range, containing English alphabets (A-Z), an underscore, a period, and a question mark.
   • The other array serves as the result range, containing the corresponding cipher characters.
5. XLOOKUP:
   Searches for each character from the input text in the lookup range and returns its corresponding ciphered character from the result range.

Cipher Formula

Here’s the complete formula for creating your ciphered messages. Enter your text in cell A2, and this formula in cell B2 will generate the ciphered output:

=ArrayFormula(
   CONCATENATE(
      XLOOKUP(
         SPLIT(REGEXREPLACE(A2&" ", "(.)", "$1 ")," "), 
         HSTACK("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","_",".","?"), 
         HSTACK("϶","К","Ў","ϸ","Д","Ϭ","Ϩ","Ж","Ͼ","Є","Ϫ","Ϯ","А","ϲ","І","Ѐ","ϴ","В","И","ϼ","Њ","ϰ","Ј","Ђ","Ќ","Ϻ","_","ל","ي"), 
         "",
      )
   )
)

Features of the Formula:

• Handles spaces with underscores (_): Spaces are not supported, so replace them with underscores in your text.
• Supports uppercase and lowercase letters: Both uppercase and lowercase letters are treated as uppercase during encryption.
• Supports question marks and periods: These characters are also included in the ciphering process.

Avoid using any other characters in the text you want to cipher.

Customizing the Cipher Codes

Want to make your cipher unique? You absolutely can!

The result range in the XLOOKUP array contains the cipher characters (϶, К, etc.). You can replace these characters with your own set. Use alphanumeric characters, symbols, or any other combination you like. Just ensure you have exactly 29 characters—one for each letter of the alphabet, plus an underscore (_), a period (.), and a question mark (?).

Deciphering the Text

To decode the ciphered message:

1. Copy the ciphered text from B2 and paste it into A3 (use Paste as Values).
2. Use this formula in B3 to decipher it:

=ArrayFormula(
   CONCATENATE(
      XLOOKUP(
         SPLIT(REGEXREPLACE(A3&" ", "(.)", "$1 ")," "), 
         HSTACK("϶","К","Ў","ϸ","Д","Ϭ","Ϩ","Ж","Ͼ","Є","Ϫ","Ϯ","А","ϲ","І","Ѐ","ϴ","В","И","ϼ","Њ","ϰ","Ј","Ђ","Ќ","Ϻ","_","ל","ي"),
         HSTACK("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","_",".","?"),  
         "",
      )
   )
)

The only change in this formula is that the lookup range and the result range in the XLOOKUP function are swapped. This allows the formula to map the ciphered characters back to their original text.

Wrap-Up

Now you know how to create your own ciphers in Google Sheets! With a single formula, you can encrypt and decrypt messages. Get creative and personalize your cipher codes to make them uniquely yours.

Try it out and have fun exploring the world of cryptography in Google Sheets!