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?

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 5In 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

If you want a direct answer to your specific question, you could use the CODE() function. With an

Ain 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

This worked PERFECTLY thank you so much!!

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.

Ended up using this for the project I'm working on, and It turned out exactly how I needed it. Thank you!!

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History