Convert Letter / Words to Given Numeric values. Please help

[ChadW]

I had originally posted this in another thread, but my initial description of what I wanted was about as clear as mud. Below is what I want to accomplish. The user enters their name in a cell, and in the next column, the numeric value is shown (based on the values list also shown on the image) How would I go about doing this please? And once again, thank you in advance for any and all help.

name_calc.xls
	A	B	C	D	E	F
1	Letters	Values
2	A	1
3	B	2
4	C	3
5	D	4
6	E	5
7	F	6			John	1685
8	G	7			Michael	4938153
9	H	8			Doe	465
10	I	9
11	J	1
12	K	2
13	L	3
14	M	4
15	N	5
16	O	6
17	P	7
18	Q	8
19	R	9
20	S	1
21	T	2
22	U	3
23	V	4
24	W	5
25	X	6
26	Y	7
27	Z	8
Values

 

[barry houdini]

You could use this formula in F7 copied down...

it requires MCONCAT for which you need the MOREFUNC addin

=MCONCAT(LOOKUP(MID(E7,ROW(INDIRECT("1:"&LEN(E7))),1),$A$2:$B$27))

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER so that braces appear around the formula in formula bar

 

[Gettingbetter]

Right hand click your sheet tab

and choose view code..

now paste in the below code (Theres probably a quicker way, but the below works fine.. It works when you type in column E rows 2 - 200)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("E2:E200")

If Target.Count > 1 Then Exit Sub

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
On Error Resume Next
Target.Offset(0, 1) = Target.Value
Target.Offset(0, 1).Replace What:=Range("A2").Value, Replacement:=Range("B2").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A3").Value, Replacement:=Range("B3").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A4").Value, Replacement:=Range("B4").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A5").Value, Replacement:=Range("B5").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A6").Value, Replacement:=Range("B6").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A7").Value, Replacement:=Range("B7").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A8").Value, Replacement:=Range("B8").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A9").Value, Replacement:=Range("B9").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A10").Value, Replacement:=Range("B10").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A11").Value, Replacement:=Range("B11").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A12").Value, Replacement:=Range("12").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A13").Value, Replacement:=Range("B13").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A14").Value, Replacement:=Range("B14").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A15").Value, Replacement:=Range("B15").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A16").Value, Replacement:=Range("B16").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A17").Value, Replacement:=Range("B17").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A18").Value, Replacement:=Range("B18").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A19").Value, Replacement:=Range("B19").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A20").Value, Replacement:=Range("B20").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A21").Value, Replacement:=Range("B21").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A22").Value, Replacement:=Range("B22").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A23").Value, Replacement:=Range("B23").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A24").Value, Replacement:=Range("B24").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A25").Value, Replacement:=Range("B25").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A26").Value, Replacement:=Range("B26").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Target.Offset(0, 1).Replace What:=Range("A27").Value, Replacement:=Range("B27").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If

End Sub

 

[Norie]

Try this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim valLen As Integer
Dim I As Integer
Dim c As String
Dim strResult As String

    If Not Intersect(Target, Columns(5)) Is Nothing Then
        valLen = Len(Target.Value)
        For I = 1 To valLen
            c = UCase(Mid(Target, I, 1))
            
            strResult = strResult & Range("B" & (Asc(c) - 63))
            
        Next I
        Target.Offset(0, 1) = strResult
        
    End If
    
End Sub

 

[ChadW]

That worked PERFECTLY! Thank you NORIE!!!!

 

[fairwinds]

Also:

=SUMPRODUCT((MOD(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-65,9)+1)*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

 

[ChadW]

Thank you all again, for your quick responses. I guess it helps when i phrase my question properly

 

[PA HS Teacher]

Row(INDIRECT("1:"&LEN(A1)))

Fairwinds,
That is so slick. I would never have thought of that.