In this video, I show you how to solve a basic cryptogram using the VLOOKUP function in Excel. We’ll do that by entering the cryptogram into Excel, one letter per cell, then we’ll create our own key and use VLOOKUP to figure out the puzzle. Don’t worry if you’re not very familiar with VLOOKUP – I will walk you through it and explain it. This might actually help you better understand how it can be used.

This video is meant to be used with cryptograms that use simple substitutions, basically the same replacement letter for each different letter in the cipher. I believe these are called aristocrat ciphers, or at least they’re very similar to those.

To quickly split sentences so each character is in its own cell, just enter the sentence into cell A1 and paste this formula =MID($A1, COLUMNS($A$1:A$1), 1) in cell B1 and copy/drag it across row 1 until each character appears in its own cell. Much easier than manually entering it! Here’s a video explaining two ways to do this:

I did not receive any compensation from the products used in this video, I just enjoyed using them and thought it would be relevant for my channel. If you’re interested in the products I mentioned, they can be found here on Amazon:

Hunt a Killer: Death at the Dive Bar –

Brain Games: Cold Case Puzzles –