Basic substitution cipher
A twitter account belonging to NSA posted this weird message one morning in the beginning of May.

It is a basic substitution cipher meaning t=w, p=a and so on. The message is "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
Download excel *.xlsm file
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
4 Responses to “Basic substitution cipher”
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.
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