convert a "word" to a numeric value

W

waynecb

New Member
Joined
Apr 4, 2013
Messages
1
I'd like to know if there is a formula/function/macro that can be applied to a column of cells in which I can obtain the numeric value of a text word.
For example. The Ascii value of the letter "D" is 68, "a" is 97, "d" is 100 and "y" is 121. So the numeric value of the word Daddy is 486 (68+97+100+100+121).
Is there a way in excel to obtain a numeric value for a word in a cell? This would allow me to measure the frequency of text values in a particular column.

Thanks in advance,

Wayne
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Jonmo1

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Try
=SUMPRODUCT(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)))
 
Rick Rothstein

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Jonmo1 said:
Try
=SUMPRODUCT(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)))
Click to expand...
I have seen that (INDEX) method of avoiding the Volatile INDIRECT function before, but is there a way to protect it against inserting new rows ahead of it (in this case, inserting a new row at Row 1)? The INDIRECT version below is immune to this action...

=SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
waynecb,

Welcome to the MrExcel forum.

Sample raw data beginning in cell A1:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Daddy</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;color: #574123;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">waynecb </td><td style="text-align: right;color: #574123;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">hiker95</td><td style="text-align: right;color: #574123;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Daddy</td><td style="text-align: right;;">486</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;color: #574123;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">waynecb </td><td style="text-align: right;color: #574123;;">777</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">hiker95</td><td style="text-align: right;color: #574123;;">641</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub GetWordValue()
' hiker95, 04/04/2013
' http://www.mrexcel.com/forum/excel-questions/695257-convert-word-numeric-value.html
Dim c As Range, i As Long, n As Long
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c <> "" Then
    n = 0
    For i = 1 To Len(c) Step 1
      n = n + Asc(Mid(c, i, 1))
    Next i
    c.Offset(, 1).Value = n
  End If
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetWordValue macro.
 
Jonmo1

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

rick rothstein said:
i have seen that (index) method of avoiding the volatile indirect function before, but is there a way to protect it against inserting new rows ahead of it (in this case, inserting a new row at row 1)? The indirect version below is immune to this action...

=sumproduct(code(mid(a1,row(indirect("a1:a"&len(a1))),1)))
Click to expand...
This should do it
=SUMPRODUCT(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)+ROW(A$1)-1))-ROW(A$1)+1,1)))
 
Last edited:
Scott Huish

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That doesn't fix the problem. I put my first name in A1 and the result was 525. When I inserted a row at A1, the result was 409.
 
Jonmo1

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Scott Huish said:
That doesn't fix the problem. I put my first name in A1 and the result was 525. When I inserted a row at A1, the result was 409.
Click to expand...

I reposted it a few minutes before your post..
I had to do the same row adjustment after the LEN as well as after the original Row.

All these row references probably defeate the purpose of avoiding Indirect...LOL.

It's just a personal choice, I hate Indirect because the Text String Ranges don't incriment when dragged.
You then have to start adding in ROW and COLUMN and ADDRESS functions to get that to work.
Indirect should only be used if it's the sheet name that needs to be varied.
 
Rick Rothstein

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Jonmo1 said:
I reposted it a few minutes before your post..
I had to do the same row adjustment after the LEN as well as after the original Row.

All these row references probably defeate the purpose of avoiding Indirect...LOL.

It's just a personal choice, I hate Indirect because the Text String Ranges don't incriment when dragged.
You then have to start adding in ROW and COLUMN and ADDRESS functions to get that to work.
Indirect should only be used if it's the sheet name that needs to be varied.
Click to expand...
Well, at least the OP now has a choice on how he wants to do this.
 
Rick Rothstein

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
hiker95 said:
After the macro:

Excel 2007
A
B
1
Daddy
486
2
3
waynecb
777
4
hiker95
641
5

<TBODY>
</TBODY>
Sheet1​
Rich (BB code): 
Option Explicit
Sub GetWordValue()
' hiker95, 04/04/2013
' http://www.mrexcel.com/forum/excel-questions/695257-convert-word-numeric-value.html
Dim c As Range, i As Long, n As Long
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c <> "" Then
    n = 0
    For i = 1 To Len(c) Step 1
      n = n + Asc(Mid(c, i, 1))
    Next i
    c.Offset(, 1).Value = n
  End If
Next c
End Sub
Click to expand...
I get your code returning 745 (which is the correct value) for "waynecb", not 777 as you show in your results table.

Here is slightly shorter, non-looping macro that will do what your macro does...
Rich (BB code): 
Sub SumLetterValues()
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp)).Offset(, 1)
    .Formula = "=IF(LEN(A1),SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT(""A1:A""&LEN(A1))),1))),"""")"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub
The addresses I highlighted in red are the ones to change if the data does not start in cell A1 (just change them to the actual start cell for the data... in the case of the "A", just change the "A" to the column letter designation for the column the data is in).
 
Last edited:
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
waynecb,

Same code, but, different results????? In the original screenshots waynecb had a trailing space character - go figure. 745 + 32 = 777

Before the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Daddy</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">waynecb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">hiker95</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Daddy</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">486</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">waynecb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">745</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">hiker95</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">641</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub GetWordValue()
' hiker95, 04/04/2013
' http://www.mrexcel.com/forum/excel-questions/695257-convert-word-numeric-value.html
Dim c As Range, i As Long, n As Long
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c <> "" Then
    n = 0
    For i = 1 To Len(c) Step 1
      n = n + Asc(Mid(c, i, 1))
    Next i
    c.Offset(, 1).Value = n
  End If
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetWordValue macro.
 
Last edited:
You must log in or register to reply here.

Similar threads

R
  • Question
Combining SUMIF & LEFT functions based on numeric values
Replies
3
Views
132
Fluff
Fluff
B
  • Question
Count unique text values with Multiple criteria
Replies
1
Views
256
jasonb75
J
I
  • Question
How to convert numbers to words in Excel with Hyphen - Minus Values
2
Replies
11
Views
405
Peter_SSs
Peter_SSs
D
  • Question
CONVERT PDF TO WORD AND FROM PAGE NO 2 TILL END PAGE
2
Replies
12
Views
261
yky
Y
I
  • Question
Sorting columns help
Replies
1
Views
101
jasonb75
J

Watch MrExcel Video

LET Function Second Look: Reusing Variables! 2326

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

Share this page

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