Numeric values to the alphabet

Question

I want to give the alphabet numeric numbers i.e. a=1, b=2,  and have a formula so when I type in any word it will add up its value. Please help!!.

Answer 1

That is pretty hard to do with a single formula. Your best bet is to build a table of the values that you want for each letter. Then use a formula to separate the word into single characters per cell (look at the MID function). then do a look up of the cell into the table you created (look at the VLOOKUP function). And, finally sum the total.

Answer 2

I'm not into mathematical formula that's the reason I have asked for help

I will not be able to progress through the steps that you have suggested.

Is there any way I can get help with these equations etc.

Answer 3

To do this smoothly and quickly you will need a VBA macro. I'll try to explain in more detail what I meant in my earlier response.

1. In a work sheet build a 2 column table, that looks like:

A 1

B 2

C 3

...

Z 26

Let's assume this in on sheet 2 starting in A1.

That is your table of values. Now To the cell where you will type your sample word, (let's assume it is in A 10) enter this formula in the first cell to the right (B10):=Left(a10,1)

then in Cell c10, enter the formula =mid(a10,2,1), and in cell D10, the formula =mid(a10,3,1)

Do this for as many cells to the right as you decide for the maximum length of the word.

Now you have the word broken down into individual letters. So next is to calculate a value for each letter. So under the cells with the letters, starting in B11, enter the =formula=vlookup(A11,sheet2!a1:b26,2)

this will give you the numeric value for each letter. Then you simply have to sum all the values in row 11 =sum(11:11)

Answer 4

To do this smoothly and quickly you will need a VBA macro. I'll try to explain in more detail what I meant in my earlier response.

1. In a work sheet build a 2 column table, that looks like:

A 1

B 2

C 3

...

Z 26

Let's assume this in on sheet 2 starting in A1.

That is your table of values. Now To the cell where you will type your sample word, (let's assume it is in A 10) enter this formula in the first cell to the right (B10):=Left(a10,1)

then in Cell c10, enter the formula =mid(a10,2,1), and in cell D10, the formula =mid(a10,3,1)

Do this for as many cells to the right as you decide for the maximum length of the word.

Now you have the word broken down into individual letters. So next is to calculate a value for each letter. So under the cells with the letters, starting in B11, enter the =formula=vlookup(A11,sheet2!a1:b26,2)

this will give you the numeric value for each letter. Then you simply have to sum all the values in row 11 =sum(11:11)

Unfortunately, your values listing will not work as there are sets of numbers from 1 to 9 and each number shows 3 letters However, I will try your proposals Thanks Gordon

Answer 5

The technique will work perfectly if there are numbers and/or symbols. just add them to the table and change the lookup formula to span the entire table range.