excel 2007

[Bluesword]

How do I assign a cell containing a single letter and a cell containing a numerical value. I want to be able to input a letter and get its assigned numerical output in a different cell. Can someone write me a formula for this?

 

[mvptomlinson]

Can you provide an example? Are you saying that if you enter the letter A in cell A1, in B1 you want to see 1? And if you put D in A1, you want to see 4 in B1?

 

[somnath_it2006]

If you have possible please put the example.

 

[mvptomlinson]

The easiest way would be:

=CODE(UPPER(A1))-64

Where A1 has the letter entered.

 

[Bluesword]

I mean any value I set. How do I set a custom numeric value for each letter of the alphabet.

 

[mvptomlinson]

Your best bet in that case would be to create a table with two columns. A through Z in the first column, then your custom number in the second column. You could then lookup the appropriate value using a VLOOKUP formula.

 

[somnath_it2006]

Or I think you can use named ranges also.
Give A to Z names to ranges and refer that ranges in your formulas etc.

 

[Blade Hunter]

Hmmmm, do your values increment in lots of 1 or is it a completely custom list.

Example

A=9, B=10, C=11

Or

A=9, B=4, C=92

If it is Option A you can use mvptomlinsons original formula to grab the ASCII code and just change the 64 to get to the starting number you require.

 

[Bluesword]

Thanks I'll try that..