summing numerical value of alphabets in a word

[asad]

Is it possible to add the numerical value of alphabets in a word in one cell?
I will explain with an example.
If I type in Asad in A1, B1 should add up like this : A+S+A+D and the total should come up to 25 (1+19+1+4).
And if I want to allocate different numerical values to alphabets, will it be different formula? For examlple, if I want A to be 10, S to be 50 and D to be 15, the answer should be 85.

Asad

 

[asad]

I tried this formula:

{=SUM(1*lookup(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26})}

, but it is not working

 

[Marcelo Branco]

Hi Asad,

Assuming your table Letter - Value in Y1:Z25 like (you can change the values as you need, eg, A=10, B=20, etc)

Y Z
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>7</TD></TR></TBODY></TABLE>
....
....
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>W</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>25</TD></TR></TBODY></TABLE>


maybe this UDF

Public Function TotalLetters(rWord As Range, rTable As Range) As Long
    Dim myLetters() As String
    Dim i As Long, aNum As Long
    
    If Len(rWord) = 0 Then Exit Function
    ReDim myLetters(1 To Len(rWord))
    
    For i = 1 To UBound(myLetters, 1)
        myLetters(i) = Mid(rWord.Text, i, 1)
        On Error Resume Next
        aNum = Application.WorksheetFunction.VLookup(myLetters(i), rTable, 2, 0)
        If Err = 0 Then TotalLetters = TotalLetters + aNum
        On Error GoTo 0
    Next i
    
End Function

Usage

A B
<TABLE style="WIDTH: 101pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=135><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=71>Asad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Forum</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>73</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>#Forum#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>73</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Maria</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>42</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Maria!!!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>42</TD></TR></TBODY></TABLE>

Formula in B1
=TotalLetters(A1,$Y$1:$Z$25)
copy down

HTH

M.

 

[asad]

Sorry for mu ignorance, but where do I put UDF function?
Is it in standard module?

 

[Aladin Akyurek]

I tried this formula:

{=SUM(1*lookup(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26})}

, but it is not working

Create a range which associates letters to values. Name the range housing the latters as LETTERS and the coresponding range of values as VALUES.

Let A1 house a string of interest like A1.

In B1 enter:

=SUMPRODUCT(SUMIF(LETTERS,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),VALUES))

 

[Marcelo Branco]

Sorry, confused with alphabet...

Table in $Y$1:$Z$26
A = 1
....
Z = 26

M.

 

[Marcelo Branco]

Sorry for mu ignorance, but where do I put UDF function?
Is it in standard module?

Yes, in a standard module

M.

 

[asad]

Thanks a lot Aladin and Marcelo.

Both solutions work perfect.
But I would prefer Aladin as it does not require any macro.

Thanks to both of you again.

Asad

 

[Marcelo Branco]

Thanks a lot Aladin and Marcelo.

Both solutions work perfect.
But I would prefer Aladin as it does not require any macro.

Thanks to both of you again.

Asad

Yes, Aladin's solution is better.

Tks for the feedback

M.

 

[asad]

Hello Aladin,

With the same problem of summing numeric values of alphabets, if I want to show different values for letters in small case and different for capital letters, what will I have to do? I will post a screen shot of my sheet. In the sheet below, I want ASAD to show 25 and Asad to show 175, but both are showing 250. How can I achieve that, if possible?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ASAD					A	1		250
2	Asad					B	2		250
3						C	3
4						D	4
5						E	5
6						F	6
7						G	7
8						H	8
9						I	9
10						J	10
11						K	11
12						L	12
13						M	13
14						N	14
15						O	15
16						P	16
17						Q	17
18						R	18
19						S	19
20						T	20
21						U	21
22						V	22
23						W	23
24						X	24
25						Y	25
26						Z	26
27						a	51
28						b	52
29						c	53
30						d	54
31						e	55
32						f	56
33						g	57
34						h	58
35						i	59
36						j	60
37						k	61
38						l	62
39						m	63
40						n	64
41						o	65
42						p	66
43						q	67
44						r	68
45						s	69
46						t	70
47						u	71
48						v	72
49						w	73
50						x	74
51						y	75
52						z	76
Sheet1

Asad