value of text in a cell in numbers

[ShyamSharma]

Hi,

I am working on a project wherein we have assigned value to all 26 characters of alphabets e.g a=1, b=1.

Requirement:- As i write some word (not letter) in the cell , it should give the value of word in any other cell.
Since i am not a VBA user, request you to suggest some function based solution.

Hope to hear soon from you on the same.

With Warm Regards

Shyam Sharma

 

[Eric W]

Welcome to the board!

Try this:

	A	B	C	D	E	F
1	Letter	Value
2	a	1
3	b	2			cab	6
4	c	3			house	68
5	d	4			zero	64
6	e	5
7	f	6
8	g	7
9	h	8
10	i	9
11	j	10
12	k	11
13	l	12
14	m	13
15	n	14
16	o	15
17	p	16
18	q	17
19	r	18
20	s	19
21	t	20
22	u	21
23	v	22
24	w	23
25	x	24
26	y	25
27	z	26

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula F3 {=SUM(SUMIF($A$2:$A$27,MID(E3,ROW(INDIRECT("1:"&LEN(E3))),1),$B$2:$B$27))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ShyamSharma]

Welcome to the board!

Try this:

	A	B	C	D	E	F
1	Letter	Value
2	a	1
3	b	2			cab	6
4	c	3			house	68
5	d	4			zero	64
6	e	5
7	f	6
8	g	7
9	h	8
10	i	9
11	j	10
12	k	11
13	l	12
14	m	13
15	n	14
16	o	15
17	p	16
18	q	17
19	r	18
20	s	19
21	t	20
22	u	21
23	v	22
24	w	23
25	x	24
26	y	25
27	z	26

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula F3 {=SUM(SUMIF($A$2:$A$27,MID(E3,ROW(INDIRECT("1:"&LEN(E3))),1),$B$2:$B$27))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Beautifully done!! Highly appreciated prompt reply!!

 

[ShyamSharma]

Beautifully done Eric!!

I was wondering what if i don't want to put numbers in other cells...

Can there be any method like if function we feed all the values in function only.

With Warm Regards

Shyam Sharma

 

[Scott Huish]

=IF(A1="","",SUMPRODUCT(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64))

Excel 2010
	A	B
1	cab	6
2	house	68
3	zero	64
4
Sheet1

Cell Formulas
Range	Formula
B1	=IF(A1="","",SUMPRODUCT(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64))
B2	=IF(A2="","",SUMPRODUCT(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-64))
B3	=IF(A3="","",SUMPRODUCT(CODE(MID(UPPER(A3),ROW(INDIRECT("1:"&LEN(A3))),1))-64))
B4	=IF(A4="","",SUMPRODUCT(CODE(MID(UPPER(A4),ROW(INDIRECT("1:"&LEN(A4))),1))-64))

 

[Eric W]

Scott's formula is the way to go if you use the values 1-26 in order. If you want to change the values, then we can tweak the formula to:

=IF(A1="","",SUM(CHOOSE(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64,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)))
with Control+Shift+Enter.

Both formulas will return incorrect results though if you have non-alphabetic characters in your word, even spaces.

 

[ShyamSharma]

Many Thanks Eric and Scott!!

Each solution is better than other......

Now the other problem which has occurred is SPACE if i give space between two words Like Shyam Sharma it gives wrong value since the value of Code(" ")=32.

Would be a great help in case i can have solution for both the options

1)

=IF(A1="","",SUMPRODUCT(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64))

<tbody>
</tbody>

2)

=IF(A1="","",SUMPRODUCT(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64))

<tbody>
</tbody>

=IF(A1="","",SUM(CHOOSE(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-64,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)))
with Control+Shift+Enter.

Hope to hear soon from you on the above.

With Warm Regards

Shyam Sharma

 

[FormR]

Hi, here are a couple of options..:

Excel 2013/2016
	A	B	C
1	Text	1)	2)
2	Shyam Sharma	126	126
3	house	68	68
4	cab	6	6
5	zero	64	64
6	# * cab house zero 123	138	138
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),CHAR(COLUMN(INDIRECT("BM:CL"))),"")),{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})
B2	{=SUM(IFERROR(SEARCH("~"&MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Scott Huish]

FormR, that second formula is very clever!!

 

[Eric W]

Agreed, that's really slick!