value of text in a cell in numbers

ShyamSharma

New Member
Joined
Dec 14, 2017
Messages
20
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
Welcome to the board!

Try this:

ABCDEF
1LetterValue
2a1
3b2cab6
4c3house68
5d4zero64
6e5
7f6
8g7
9h8
10i9
11j10
12k11
13l12
14m13
15n14
16o15
17p16
18q17
19r18
20s19
21t20
22u21
23v22
24w23
25x24
26y25
27z26

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

Array Formulas
CellFormula
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

New Member
Joined
Dec 14, 2017
Messages
20
Welcome to the board!

Try this:

ABCDEF
1LetterValue
2a1
3b2cab6
4c3house68
5d4zero64
6e5
7f6
8g7
9h8
10i9
11j10
12k11
13l12
14m13
15n14
16o15
17p16
18q17
19r18
20s19
21t20
22u21
23v22
24w23
25x24
26y25
27z26

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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

New Member
Joined
Dec 14, 2017
Messages
20
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"> cab </td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";"> house </td><td style="text-align: right;;">68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";"> zero </td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IF(<font color="Blue">A1="","",SUMPRODUCT(<font color="Red">CODE(<font color="Green">MID(<font color="Purple">UPPER(<font color="Teal">A1</font>),ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:"&LEN(<font color="Navy">A1</font>)</font>)</font>),1</font>)</font>)-64</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">A2="","",SUMPRODUCT(<font color="Red">CODE(<font color="Green">MID(<font color="Purple">UPPER(<font color="Teal">A2</font>),ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:"&LEN(<font color="Navy">A2</font>)</font>)</font>),1</font>)</font>)-64</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=IF(<font color="Blue">A3="","",SUMPRODUCT(<font color="Red">CODE(<font color="Green">MID(<font color="Purple">UPPER(<font color="Teal">A3</font>),ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:"&LEN(<font color="Navy">A3</font>)</font>)</font>),1</font>)</font>)-64</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=IF(<font color="Blue">A4="","",SUMPRODUCT(<font color="Red">CODE(<font color="Green">MID(<font color="Purple">UPPER(<font color="Teal">A4</font>),ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:"&LEN(<font color="Navy">A4</font>)</font>)</font>),1</font>)</font>)-64</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
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

New Member
Joined
Dec 14, 2017
Messages
20

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, here are a couple of options..:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Text</td><td style=";">1)</td><td style=";">2)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Shyam Sharma</td><td style="text-align: right;background-color: #FFFF00;;">126</td><td style="text-align: right;background-color: #FFFF00;;">126</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">house</td><td style="text-align: right;;">68</td><td style="text-align: right;;">68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">cab</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">zero</td><td style="text-align: right;;">64</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";"># * cab house zero 123</td><td style="text-align: right;;">138</td><td style="text-align: right;;">138</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">LEN(<font color="Red">A2</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">UPPER(<font color="Purple">A2</font>),CHAR(<font color="Purple">COLUMN(<font color="Teal">INDIRECT(<font color="#FF00FF">"BM:CL"</font>)</font>)</font>),""</font>)</font>),{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}</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=SUM(<font color="Blue">IFERROR(<font color="Red">SEARCH(<font color="Green">"~"&MID(<font color="Purple">A2,ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:" & LEN(<font color="Navy">A2</font>)</font>)</font>),1</font>),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
FormR, that second formula is very clever!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,968
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top