## Basic substitution cipher

*Article last 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

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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