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
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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.
Sub Encryptmin() Dim enc As String Dim res As String enc = Range("B2") For i = 1 To Len(enc) If Asc(UCase(Mid(enc, i, 1))) < 65 Or Asc(UCase(Mid(enc, i, 1))) > 90 Then 'NOT A-Z res = res & Mid(enc, i, 1) Else res = res & Chr(Asc(UCase(Mid(enc, i, 1))) - 65 + Range("b8") Mod 26 + 65) End If Next i Range("B5") = res End SubTorstein,
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