Simple Cipher in Excel?

[HappyCampers 10]

I am trying to do a simple cipher - offset a string of letter by 2 characters automatically

 

so a becomes c, b becomes d etc

 

any suggestions for the cell formula to do that as I am getting in a right pickle

 

tia

[Afilsdesigne 10]

So with the letter a in cell a1, use formula =CHAR(CODE(A1)+2)

 

Job done (returns c).

 

Encrypt / Decrypt any 6 character string see: http://www.dt4u.com/filearea/cryptography.xls

 

Bit clunky but works. You could easily change the offset for any number.

Edited February 13, 2013 by Afilsdesigne

[maggi 10]

Open your spreadsheet and press alt-F11 to get the Visual Basic editor. Choose Insert/Module from the menu and paste in the code below. Press alt-Q to close the editor.

 

You can use =Rot("My string",2) or =Rot(F3,2) to encode your string with offset 2.

 

Option Explicit
Function Rot(codeString, Optional offset)
'Simple encrypting. Rotate original string by 'offset' characters
'By maggi 13/2/13

   Dim charIndex As Long, charCode As Integer, alpha As Integer

   If IsMissing(offset) Then
       offset = 0                                      ' ignore missing values
   End If
   If offset > 26 Or offset < 0 Then
       offset = 0                                      ' ignore invalid values
   End If

   For charIndex = 1 To Len(codeString)                ' for each character in the string
       charCode = Asc(Mid$(codeString, charIndex, 1))  ' get the character code
                                                       ' only use an offset if the character is A-Z or a-z
       If (charCode >= Asc("A") And charCode <= Asc("Z")) Or (charCode >= Asc("a") And charCode <= Asc("z")) Then

           If charCode >= Asc("a") Then                ' are we in upper or lower case?
               alpha = Asc("a")
           Else
               alpha = Asc("A")
           End If

           charCode = charCode + offset                ' add on 'offset' characters

           If charCode >= alpha + 26 Then              ' have we gone past the end of our case range?
              charCode = charCode - 26
           End If

       End If
       Mid$(codeString, charIndex, 1) = Chr(charCode)  ' substitute the new character
   Next charIndex
   Rot = codeString

End Function

Edited June 23, 2013 by maggi