• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Quick and easy way to encode text in Excel

Hello,

This is sort of an odd question but it might be kind of fun too.

Using Excel (2007), does anyone know of a quick and easy way to encode text so that to others, it is just incomprehensible gobbledygook but for those in-the-know, it can be transformed into the real or actual text in just a step or two?

Thanks

PS  I was thinking of sending away to K-TEL to order one of their Slicko-Dicko Invisible Ink & Decoder sets but thought I should inquire here first.   :)
0
Steve_Brady
Asked:
Steve_Brady
  • 6
  • 4
  • 2
  • +2
2 Solutions
 
rspahitzCommented:
It really depends on how complex you want the encryption to be.
A simple one could be to take every character and replace it with the ASCII code (in hex) of that character.
For something more elaborate, you can subscribe to the PGP system and get a private key that will allow you to encrypt the data so that nobody without the key can encrypt it.
Another option is to simply compress the data, but if there's not much, that may not work very effectively.
0
 
Steve_BradyAuthor Commented:
Thank for the response.

>>A simple one could be to take every character and replace it with the ASCII code (in hex) of that character.

The ASCII idea is ample for what I want to do but how would you do that?  

For example, if I enter the word "and" in A1 and enter =CODE(A1) in A2, the result is 97.  Then of course, entering =CHAR(A2) in A3 results in only the letter "a" being displayed in A3.  As such, it seems that just toggling back and forth using =CODE() and =CHAR() functiomns is not the solution.

Is there a way to quickly toggle between ASCII and alphanumeric characters without losing content?

Thanks
0
 
dlmilleCommented:
Steve - you need a function to translate EVERY character, not just the first.

Try something like this functions

Public Function trans_toAsc(rng As Range)
Dim myString As String, myChar As String
Dim i As Long

    If rng.Count > 1 Then
        trans_toAsc = "#####"
        Exit Function
    End If
    
    myString = rng.Value
    For i = 1 To Len(myString)
       myChar = Mid(myString, i, 1)
       myChar = Format(Asc(myChar), "000")
       trans_toAsc = trans_toAsc & myChar
    Next i
    
End Function
Public Function trans_fromAsc(rng As Range)
Dim myString As String, myChar As String
Dim i As Long

    If rng.Count > 1 Then
        trans_fromAsc = "#####"
        Exit Function
    End If
    
    myString = rng.Value
    For i = 1 To Len(myString) Step 3
       myChar = Mid(myString, i, 3)
       myChar = Format(Chr(myChar), "000")
       trans_fromAsc = trans_fromAsc & myChar
    Next i
    
    
End Function

Open in new window


See attached spreadsheet example...

Enjoy!

Dave
encrypt-simple-r1.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
How it works - the function checks to ensure ONE CELL is entered into the function

translate_toAsc(rng as range)

and

translate_fromAsc(rng as range)

translate_toAsc()  goes though each string, one character at a time to convert to ASCII code, concatenating the string result as it goes.  Note the Format command to ensure the Ascii string is 3 characters long (as the ASCII CODE function supports codes from 1-255) - so this makes it easier for the function translate_fromAsc() to translate BACK to text (parsing 3 characters at a time, this time).

Cheers,  Dave
0
 
dlmilleCommented:
You could also create a multipler KEY that no one else knows.  Or ADD to the code with a key.   Here's the example with a KEY adding 5 to the CODE, so if someone were to translate to ascii, they'd still be off, unless they knew to add 5:



 
Public Function trans_toAsc(rng As Range)
Dim myString As String, myChar As String
Dim i As Long

    If rng.Count > 1 Then
        trans_toAsc = "#####"
        Exit Function
    End If
    
    myString = rng.Value
    For i = 1 To Len(myString)
       myChar = Mid(myString, i, 1)
       myChar = Format(Asc(myChar) + 5, "000")
       trans_toAsc = trans_toAsc & myChar
    Next i
    
End Function
Public Function trans_fromAsc(rng As Range)
Dim myString As String, myChar As String
Dim i As Long

    If rng.Count > 1 Then
        trans_fromAsc = "#####"
        Exit Function
    End If
    
    myString = rng.Value
    For i = 1 To Len(myString) Step 3
       myChar = Mid(myString, i, 3)
       myChar = Chr(myChar - 5)
       trans_fromAsc = trans_fromAsc & myChar
    Next i

Open in new window


Also, I improved the translate_toAsc() as the Format command is not needed and was throwing numeric conversion off...

See attached file with the update, as well...

Dave
encrypt-simple-r2.xls
0
 
rspahitzCommented:
And using Dave's functions, if you apply them in every cell of a "parallel" sheet that references the original sheet (which you should hide--right-click the tab and select Hide) might just handle it.

I.e. Cell A1=translate_toAsc(OrigDataSheet!A1)
Cell A2=translate_toAsc(OrigDataSheet!A2)
Cell A3=translate_toAsc(OrigDataSheet!A3)
Cell A4=translate_toAsc(OrigDataSheet!A4)
...
Cell B1=translate_toAsc(OrigDataSheet!B1)
...
for every cell needed.
0
 
rspahitzCommented:
Hmmm...just looking at that, if you don't want people to see the info on a page, you can simply hide the entire spreadsheet and the casual user will not know it's there (even if they know how to unhide sheets)

Further, you could set the formulas on a page to be hidden, then turn protection on (possibly with a password) and you have pretty decent control over these pieces.

I guess it really goes down to why you want to encrypt the data...is it just a few select cells? Then store the original text somewhere (like a hidden sheet) and hide the formula (which encrypts as I mentioned above) so all that you see is the string of digits.
0
 
dlmilleCommented:
Good idea!

If you really want to get carried away, lol, send the coded sheet to someone.

Then, send the translate function in the form of an add-in, where you've protected the code.  You could even have 2 parameters on the coding, decoding functions - the range, and the KEY.  That way, you could send separately the KEY.

Here's one step further, where the key to add to the code is a parameter...

 

and finally an encoding subroutine - you knew between rsphahitz and I we'd be noodling some creativity, lol...

 
Public Function trans_toAsc(rng As Range, key As Integer) As Variant
Dim myString As String, myChar As String
Dim i As Long

    If rng.Count > 1 Then
        trans_toAsc = "#####"
        Exit Function
    End If
    
    trans_toAsc = "'" 'so large codes don't convert to xxxE+1 type format
    myString = rng.Value
    For i = 1 To Len(myString)
       myChar = Mid(myString, i, 1)
       myChar = Format(Asc(myChar) + key, "000")
       trans_toAsc = trans_toAsc & myChar
    Next i
    
End Function
Public Function trans_fromAsc(rng As Range, key As Integer) As Variant
Dim myString As String, myChar As String
Dim i As Long


    If rng.Count > 1 Then
        trans_fromAsc = "#####"
        Exit Function
    End If
    
    myString = Right(rng.Value, Len(rng.Value) - 1) 'eliminate first char plugged on encoding
    For i = 1 To Len(myString) Step 3
       myChar = Mid(myString, i, 3)
       myChar = Chr(myChar - key)
       trans_fromAsc = trans_fromAsc & myChar
    Next i
    
    
End Function
Sub encodeSheet()
Dim mycell As Range
Dim key As Integer

    key = InputBox("Enter Key from -100 to 100", "Enter Cypher Key", 0)
    For Each mycell In ActiveSheet.UsedRange
        If Left(mycell.Formula, 1) <> "=" Then 'don't translate formulas/equations, just constants
            mycell.Value = trans_toAsc(mycell, key)
        End If
    Next mycell
            
End Sub
Sub decodeSheet()
Dim mycell As Range
Dim key As Integer

    key = InputBox("Enter Key from -100 to 100", "Enter Cypher Key", 0)
    For Each mycell In ActiveSheet.UsedRange
        If Left(mycell.Formula, 1) <> "=" Then 'don't translate formulas/equations, just constants
            mycell.Value = trans_fromAsc(mycell, key)
        End If
    Next mycell

End Sub

Open in new window


Go to the Encode/Decode tab in the attached and hit DECODE and use 5 when prompted for the Key.


of course, with some refinement, you could encode with a first key, then encode again with a second key.  Unwinding would require the keys in reverse order.  I didn't test that, but just an idea to really jumble it up!

dave
encrypt-simple-r3.xls
0
 
dlmilleCommented:
One minor bug.

this one encode/decode working great.  for some reason I hit undo or didn't save the working file before posting.  sorry about that!

Dave
encrypt-simple-r4.xls
0
 
dlmilleCommented:
Ok, last one.  I did some debugging on multiple encoding (the app was converting my text strings to numbers along the way, so needed to prefix with a "'" to sort that out).

I encoded the sheet with a key of 5, then encoded it again with 4.

So, pull it up and decode sheet with 4, then decode again with 5.

So multiple code conversions seem to work without EXTENSIVE testing, lol.

Try out the attached.

Enjoy!

Dave
encrypt-simple-r5.xls
0
 
SiddharthRoutCommented:
Steve if you are interested I can take you through an interesting way to encrypt your data which is almost unhackable.

Sid
0
 
aikimarkCommented:
@Steve_Brady

Do you need encryption or just encoding?

How much 'content protection' do you need?
0
 
aikimarkCommented:
I assume you know about the built-in encryption.  The steps are listed here:
http://www.stanford.edu/~ericn/Encrypt/Encrypt_Excel_Descr.htm

Note: this encrypts the entire workbook, not just a range or worksheet.

Nice article on Excel encryption strength:
http://www.oraxcel.com/projects/encoffice/help/How_safe_is_Excel_encryption.html
0
 
Steve_BradyAuthor Commented:
Great feedback folks!  I wish I had about 2000 points to cover all contributors and all ideas! Thanks.
0
 
rspahitzCommented:
Thanks Steve...I agree it was a good discussion.
(Points are secondary to me.)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now