## Basic substitution cipher

*Article updated on May 20, 2014*

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

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 […]### 4 Responses to “Basic substitution cipher”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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