Build a Pangram Machine in Excel

If you're anything like me — a somewhat compulsive, geeked-out word nerd — your psychiatrist probably has you on some pretty strong tranquilizers most of the time. But in your more lucid moments, you might find yourself playing around with words and letters and seeing what new things you can do with them.

Words are, after all, the best toys.


One thing I enjoy (perhaps a little too much) is coming up with pangrams, that is, sentences that contain each of the 26 letters of the alphabet at least once, like the famous "The quick brown fox jumps over the lazy dog." But it can get tedious checking your sentences against the alphabet all the time. So I've developed a quicker way to build pangrams, and you can use it, too. All it takes is Microsoft Excel and a little OCD.

FYI: Feel free to type out these formulas yourself (it can help you learn how the formulas work), but you should be able to literally copy and paste the formulas from this post into their proper places in Excel. Or, you could just wait till the end and download the finished product.

The basic pangram checker

1. Start with a blank Excel spreadsheet. Starting in cell A2, enter the letters of the alphabet, one per cell, down that column.
   There's probably an auto-fill way to do this, but I couldn't find it, and looking for it would take more time than just doing it manually.
2. Widen column C considerably, and then add a border around cell C2.
   C2 is where you'll enter your test sentences that might be pangrams. The border just makes it easier to find.
   
   
3. Select cell B2, type =IF(ISERR(SEARCH(A2,$C$2)),0,1), and press Ctrl+Enter.
   
   
   
   The dollar signs are important. The reference to cell A2 needs to be a relative reference so it will change when you copy the formula from one cell to the next, but each copy needs to lock in to cell C2, so it requires an absolute reference. The dollar signs do that.
   
   Pressing Ctrl+Enter is just like pressing Enter except Excel stays on the same cell. When you press Ctrl+Enter, the number 0 should appear in the cell.
4. Put the cursor in the lower-right corner of cell B2 (while it's still selected). When the cursor changes (usually from a white plus to a thinner, black plus unless you've overcustomized your mouse pointers), hold down the left mouse button and drag down to B27.
   When you let go of the mouse button, you'll find that you've copied the formula into all those cells. If you type something in C2 now, you can get a basic idea of how this works and where I'm going.
   
   
5. Select cell B1 and type =SUM(B2:B27)=26.
   
   This formula simply adds all the ones and zeroes in that column and displays TRUE if the sum is 26 and FALSE otherwise.

This is the basic setup. Now, if the sentence you type in C2 is really a pangram, cell B1 will say TRUE. If not, it'll say FALSE. This is the basic pangram checker that will simply tell you whether or not all 26 letters appear in the sentence you typed. In no time, you could be building original pangrams like these two that took me less than 5 minutes to figure out:

Five dozen Jews pray for Chuck to quit making blue tuxedos.
Perhaps Gaga wants a kumquat tuxedo because she likes jazzy flavors?

When you've created one, you can copy it to the other empty slots in column C and keep a running list of your original pangrams. For, you know, tax purposes.

"But Logophilius," you say, "This is too basic. It isn't nearly geeky enough, and it doesn't even tell me which letters are missing. Surely you've figured out how to go completely overboard with this!"

Right you are! Here is how you can really geek it up and give your pangram machine, in the words of Tim Allen, "More Cocaine!" But first . . .

Add a character counter

The general objective of pangramification is to try to create the shortest sentence you can that uses all the letters of the alphabet. But you don't want to manually count all the letters in your sentences. Luckily, Excel provides the LEN (short for length) function, which tells you how many characters are in a cell. Unluckily, LEN includes spaces in that character count. There's an easy way around that, but it makes the formula more complicated.

I use the empty cell C1 for the character count, but you could put this formula anywhere on the worksheet and it'll still work:

=LEN(SUBSTITUTE(C2," ",""))

Important: In this formula, there should be a space between the first pair of quotation marks and no space between the second pair.

The SUBSTITUTE part of the formula replaces (in memory) all the spaces in the sentence with nothing — which is to say it eliminates all the spaces between words — and the LEN function counts the number of characters in that spaceless version.

Remember: Punctuation has character, and punctuation marks are characters. If you include any punctuation, those marks are included in the character count.

And now. . .

List the missing letters

1. Select cell B2 and change the formula to this: =IF(ISERR(SEARCH(A2,$C$2)),A2,"")
   This should look familiar. It has the same structure as what was there before, but now, instead of returning a 0 or 1, it returns the letter in question if it isn't in your example sentence and returns a blank cell otherwise.
2. Select cell B2, press Ctrl+C to copy it the formula, and then select B3 and paste it in.
   Excel will automatically change the relative references. You have to add one little thing to this formula before you copy it to the rest of the cells, but it's pretty easy.
3. Select cell B3 and add B2& right after the equals sign.
   The finished formula should look like this: =B2&IF(ISERR(SEARCH(A3,$C$2)),A3,"")
4. Select cell B3 once again, grab that bottom-right corner, and drag the formula all the way down to B27.

You should now see, in that last cell (B27), a list of all the letters that are not in your pangram test sentence.

Put it all in one place

The piéce de résistance is to put all the information into one place. You already have a cell that shows which letters are missing from your test pangram, and you have a simple formula for showing how many letters are in your pangram. You can put all that information in one place.

I'm going to skip the step-by-step and just show you the finished formula, which I put in cell C1, right above where I test my possible pangrams:

=IF(LEN(B27)>0,B27,"Success! Characters: " & LEN(SUBSTITUTE(C2," ","")))

You can also now delete the formula in cell B1 and then gussy the page up all you want. For example:

• Hide columns A and B, so you only have the column of pangrams with the pangram checker at the top.
• If you prefer capital letters, change the beginning of the previous formula to =IF(LEN(B27)>0,UPPER(B27),. . .
• Add boldface, italics, font colors, fill colors, and goofy fonts to prettify the thing.
• Use conditional formatting so that the page (or maybe just a couple of important cells) lights up when you hit on a real pangram.

Enjoy the geekiness, and know that if you go through all this, it will *ahem* not be in vain.

You can download (I hope) my spreadsheet, AndysWordFun, with my completed (and decorated) version from my public DropBox. I'm sure I'll be adding other weird little tools to it as time goes on as well.