Solved Can I create an encryption algorithm using Microsoft office?

January 29, 2016 at 17:54:48
Specs: Windows
I am trying to create a resource where I can decrypt information using a certain algorithm. I want to be able to input values and have excel output the corresponding decrypted values. For example if I type in A in a cell it will output the number 1, but if i type B it will output 2

So if I type: A in A1, B in B1, C in C1 etc
It will output 1, 2, 3, etc.

is this possible in excelor any Microsoft office product? If not any recommendations on what to use?


See More: Can I create an encryption algorithm using Microsoft office?

Report •


#1
January 29, 2016 at 18:15:58
✔ Best Answer
You could probably use the VLOOKUP() function with some type of table of conversions and get what your looking for.

With your sheet looking like this, with your Conversion Table in Columns D & E

    A      B       C       D       E
1)  C      3               A       1
2)  b      2               B       2
3)  E      5               C       3
4)                         D       4
5)                         E       5

In Cell B1 enter the formula:

=IF(A1="","",VLOOKUP(A1,$D$1:$E$26,2,))

Drag down as many rows as needed.

Then when you enter a letter in Column A, the formula
looks up the corresponding code in your table array
and returns the associated number.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#2
January 29, 2016 at 18:56:16
If you want a direct answer to your specific question, you could use the CODE() function.

With an A in A1, =CODE(A1) would return 65, therefore =CODE(A1)-64 would return 1.

Since the ASCII code for A-Z is 65-90, dragging =CODE(A1)-64 down alongside A-Z would return 1-26, which is what you asked for.

Mike's VLOOKUP solution is certainly more versatile, but of course you have to build the lookup table first. I offer the CODE() option only to show that the best/most efficient solution depends on what you are really trying to do. With more specifics, we might be able to offer something that directly fits your requirements and works with the least amount of effort.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
January 29, 2016 at 21:46:36
This worked PERFECTLY thank you so much!!

Report •

Related Solutions

#4
January 30, 2016 at 06:07:51
Hey Mike,

It looks like one of us made him happy. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 30, 2016 at 06:28:30
I'm anxiously awaiting his return to mark Best Answer.... :-)

MIKE

http://www.skeptic.com/


Report •

#6
January 31, 2016 at 18:46:29
Ended up using this for the project I'm working on, and It turned out exactly how I needed it. Thank you!!

Report •

#7
February 1, 2016 at 03:35:39
Glad we could help.
If you need any further assistance, please ask.

MIKE

http://www.skeptic.com/


Report •

Ask Question