convert a "word" to a numeric value

[waynecb]

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

 

[Jonmo1]

Welcome to the board.

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

 

[Rick Rothstein]

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

 

[hiker95]

waynecb,

Welcome to the MrExcel forum.

Sample raw data beginning in cell A1:

Excel 2007
	A	B
1	Daddy
2
3	waynecb
4	hiker95
5
Sheet1

After the macro:

Excel 2007
	A	B
1	Daddy	486
2
3	waynecb	777
4	hiker95	641
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
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.

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]

ADVERTISEMENT

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

 

[Scott Huish]

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]

ADVERTISEMENT

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.

 

[Rick Rothstein]

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.

 

[Rick Rothstein]

After the macro:

Excel 2007

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

<TBODY>
</TBODY>

Sheet1​

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

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

 

[hiker95]

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
	A	B
1	Daddy
2
3	waynecb
4	hiker95
5
Sheet1

After the macro:

Excel 2007
	A	B
1	Daddy	486
2
3	waynecb	745
4	hiker95	641
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).

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.