from Survival Blog:
I am an experienced programmer with a lot of time spent in Excel everyday for my paying job. East Sierra Sage’s article on Cipher Security got me thinking again about One Time Pads and other secure message techniques. I really enjoy automating things with Excel’s powerful formulas and macros. So I set off to create a tool for a One Time Pad spreadsheet that would create the One Time Pads and also encode and decode the messages being sent.
How to Use a One Time Pad (OTP)
You will need a Shift Chart and an OTP Chart as the basic components for this type of message security. The Shift Chart is used to look up a character in your message and then offset or shift to a position in the Shift Chart using another character found in the OTP Chart. Your message will be encoded one character at a time.
To Encode Your Message
To encode your message, you take the first letter of your message and go down the left most column of your Shift Chart. When you find that letter in your Shift Chart, you will then find the next character available in your OTP Chart. To do that, go to the row of your OTP Chart and find the next unused character. If you have already used all of a row, just go down to the next available row. With your OTP character you find it by going across the top row of the Shift Chart. Use the character where the column and row intersect as your encoded letter. Cross off your OTP character in the OTP Chart, so that you will not ever use it again. Continue with that process until you have your entire message encoded.
To Decode a Received Message
To decode a received message, find your next OTP character in the OTP Chart and then find it by going across the top of your Shift Chart. Then look for the first received character in the message and go down that column until you find that character. Follow that row all the way to the left to see what the decoded message character is. Cross off your OTP and repeat that process until your message has been decoded.
Note: If you are new to this type of technique, it might be a good idea to print off the example picture in this article and then reread this section on how to use and OTP.
Creating the OTP Chart
Decide What Characters To Use
On a tab called OneTimePad, I allow the user to decide what characters they wish to use for the OTP. I decided to allow for 50 unique characters. The user can choose basically anything on the keyboard except an asterisk, a question mark, or a space. (Each of those characters is more difficult to program around.) These 50 characters are what will be used to create your OTP as well as the characters used in your encoded message.
Note About Use of High Numbered ASCII Codes
Advanced Note: You can even use some of the higher numbered ASCII codes to create something that is visibly harder to discern because they are characters or symbols that we aren’t used to looking at. (Examples of these are œ,ž,Ÿ,¡,¢,£,¤, and ¥.) If you are just getting started, I would suggest sticking with just uppercase letters, 0-9, and a few of the characters above the numbers on the keyboard, like in the example.