alphabet as Numbers?

**Loriandme69** · 11-21-2005, 06:58 PM

I want to associate letters of the alphabet with numbers so that when a letter is put in a cell, a numerical value can be associated. Sort of like a phone pad-I want it so that you could put the letter in a cell and a designated number would automatiocaly show up in another cell. Is there a way to create a formula that can do this? Help would be greatly appreciated. Thank you.

**Paul Sheppard** · 11-21-2005, 07:43 PM

Originally Posted by Loriandme69

I want to associate letters of the alphabet with numbers so that when a letter is put in a cell, a numerical value can be associated. Sort of like a phone pad-I want it so that you could put the letter in a cell and a designated number would automatiocaly show up in another cell. Is there a way to create a formula that can do this? Help would be greatly appreciated. Thank you.

Hi Loriandme69

You could create a lookup table with the letters a-z and the corresponding numbers

and then use the LOOKUP function to to return the corresponding numeric value when a letter is input

Gord Dibben · 11-21-2005, 07:55 PM

In B1 enter this formula

=CHAR(A1 + 96)

Enter a number from 1 to 26 in A1

If numbers were not 1 through 26 you could use a VLOOKUP table.

Enter numbers in A1:A26

Enter letters a through z in Column B

In C1 enter a number

In D1 enter this formula =VLOOKUP(C1,$A$1:$B$26,2,FALSE)

Gord Dibben Excel MVP

On Mon, 21 Nov 2005 16:58:59 -0600, Loriandme69
<[email protected]> wrote:

>
>I want to associate letters of the alphabet with numbers so that when a
>letter is put in a cell, a numerical value can be associated. Sort of
>like a phone pad-I want it so that you could put the letter in a cell
>and a designated number would automatiocaly show up in another cell. Is
>there a way to create a formula that can do this? Help would be greatly
>appreciated. Thank you.

Jay · 11-21-2005, 07:55 PM

> I want to associate letters of the alphabet with numbers so that when a
> letter is put in a cell, a numerical value can be associated. Sort of
> like a phone pad-I want it so that you could put the letter in a cell
> and a designated number would automatiocaly show up in another cell. ...

One way is to use VLOOKUP.

Get started by putting the settings on Sheet2.
- Put the letters in column A of Sheet2.
- Put the corresponding numbers in column B of Sheet2.

If the letter goes into A1 on Sheet1, put this in B1 of Sheet1:
=VLOOKUP(A1,Sheet2!A:B,2,FALSE)

Depending on your case-(in)sensitivity needs, you might want to use UPPER()
or LOWER().

Gord Dibben · 11-21-2005, 08:00 PM

Ooopps! Backwards.

Go with the VLOOKUP table and switch the letters and numbers columns.

Enter a letter in C1.

Gord

On Mon, 21 Nov 2005 15:49:21 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>In B1 enter this formula
>
>=CHAR(A1 + 96)
>
>Enter a number from 1 to 26 in A1
>
>If numbers were not 1 through 26 you could use a VLOOKUP table.
>
>Enter numbers in A1:A26
>
>Enter letters a through z in Column B
>
>In C1 enter a number
>
>In D1 enter this formula =VLOOKUP(C1,$A$1:$B$26,2,FALSE)
>
>
>Gord Dibben Excel MVP
>
>On Mon, 21 Nov 2005 16:58:59 -0600, Loriandme69
><[email protected]> wrote:
>
>>
>>I want to associate letters of the alphabet with numbers so that when a
>>letter is put in a cell, a numerical value can be associated. Sort of
>>like a phone pad-I want it so that you could put the letter in a cell
>>and a designated number would automatiocaly show up in another cell. Is
>>there a way to create a formula that can do this? Help would be greatly
>>appreciated. Thank you.