Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    erehwon, Cambridgeshire, United Kingdom
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    next letter (2002 SP3)

    Complete Excel novice :-) I wonder if I can use Excel for some primitive spy-type codes. So that CATS (a letter in each cell would be ok ) becomes DBUT by using the next character in the alphabet. What if I want to shift three (for example) characters? If some kind soul can point me in the right direction, I might be able to move ahead.

    (If this question is more suited to Scuttlebut, please feel free to move it)
    Thanks
    ChrisP

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: next letter (2002 SP3)

    You can use a custom VBA function for this. The attached sample workbook contains such a function:

    Public Function Shift(strText As String, intShift As Integer) As String
    Dim i As Integer
    Dim c As Integer
    For i = 1 To Len(strText)
    c = Asc(Mid(strText, i, 1))
    If c > 64 And c < 91 Then
    c = (c + 39 + intShift) Mod 26 + 65
    ElseIf c > 96 And c < 123 Then
    c = (c + 33 + intShift) Mod 26 + 97
    End If
    Shift = Shift & Chrę
    Next i
    End Function

    Change the text in B1 or the shift in B2 to see the result (the shift can be positive or negative, e.g. use -3 to shift D to A etc.)
    Attached Files Attached Files

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: next letter (2002 SP3)

    The User Defined Function below should do what you asked:

    <code>
    Public Function SPYCODE(strTxt As String, iShift As Integer) As String
    Dim I As Integer, iC As Integer
    Dim strWK As String
    strWK = strTxt
    For I = 1 To Len(strWK)
    iC = Asc(Mid(strWK, I, 1))
    If iC >= 65 And iC <= 90 Then
    iC = iC + iShift
    If iC > 90 Then
    iC = iC - 26
    End If
    Else
    If iC >= 97 And iC <= 122 Then
    iC = iC + iShift
    If iC > 122 Then
    iC = iC - 26
    End If
    End If
    End If
    Mid(strWK, I, 1) = Chr(iC)
    Next I
    SPYCODE = strWK
    End Function
    </code>

    The function expects two arguments. The first is the string to be shifted and the second is the number of characters to shift. If the original string CATS is in cell A1, then placing the following in cell B1 will result in DBUT.

    <code>
    =spycode(A1,1)
    </code>


    Changing the above to:

    <code>
    =spycode(A1,3)
    </code>

    will result in FDWV. If the shift results in shifting past the letter Z, the function wraps back to A.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: next letter (2002 SP3)

    Hi Hans
    I must admire the elegance of your function; but I cannot fathom the logic behind the two magic numbers 39 and 33 . Can you help me please?
    <hr> <pre> c = (c + 39 + intShift) Mod 26 + 65
    ElseIf c > 96 And c < 123 Then
    c = (c + 33 + intShift) Mod 26 + 97</pre>

    <hr>
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: next letter (2002 SP3)

    Thank you Hans.
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: next letter (2002 SP3)

    I originally had -65 and -97. But I wanted to make it possible to use negative shifts. With a negative shift, the result of c - 65 + intShift or c - 97 + intShift could become negative.

    The Mod operator in VBA has a strange quirk: -3 Mod 5 evaluates to -3, whereas a mathematician would expect 2. This would cause "wrapping" the alphabet to fail. To get around this, I added a multiple of 26 (the number of letters in the alphabet) to -65 and -97 so that the result is positive for "reasonable" negative shifts. 39 = -65 + 4 * 26 and 33 = -97 + 5 * 26.

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Location
    erehwon, Cambridgeshire, United Kingdom
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: next letter (2002 SP3)

    That sound is from my fingernails as I try to hang on :-)
    Thanks to all. I will read and try to learn.
    ChrisP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •