# convert a "word" to a numeric value

#### waynecb

##### New Member
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.

Wayne

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the board.

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

Try
=SUMPRODUCT(CODE(MID(A1,ROW(A\$1:INDEX(A:A,LEN(A1))),1)))
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)))

waynecb,

Welcome to the MrExcel forum.

Sample raw data beginning in cell A1:

Excel 2007
AB
2
3waynecb
4hiker95
5
Sheet1

After the macro:

Excel 2007
AB
2
3waynecb777
4hiker95641
5
Sheet1

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
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.

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)))
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:
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.

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.

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.

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.
Well, at least the OP now has a choice on how he wants to do this.

After the macro:

Excel 2007
A
B
1
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``````
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:
waynecb,

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

Before the macro:

Excel 2007
AB
2
3waynecb
4hiker95
5
Sheet1

After the macro:

Excel 2007
AB
2
3waynecb745
4hiker95641
5
Sheet1

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:

Replies
3
Views
298
Replies
4
Views
1K
Replies
21
Views
944
Replies
9
Views
507
Replies
2
Views
165

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### 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.

### Which adblocker are you using?

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

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