Forum Discussion
Excel -- Converting letters to number
Hi Jason,
If you familiar with VBA in Excel you can create a custom function to do this and use it in the worksheet.
This is my suggested function:
Option Compare Text
Function LettersToNumbers(text As String) As Integer
Dim i As Integer
Dim result As String
For i = 1 To Len(text)
Select Case Mid(text, i, 1)
Case "a"
result = result & 1
Case "b"
result = result & 2
Case "c"
result = result & 3
Case "d"
result = result & 4
Case "e"
result = result & 5
Case "f"
result = result & 6
Case "g"
result = result & 7
Case "h"
result = result & 8
Case "i"
result = result & 9
Case "j"
result = result & 0
End Select
Next
LettersToNumbers = result
End FunctionPlease follow this https://www.excel-easy.com/vba/examples/user-defined-function.html to figure out how to insert this function into your own workbook.
Also, you can place it in the Personal Macro Workbook so that the function will be available in all open workbook and this is described here in this https://www.myonlinetraininghub.com/moving-vba-code-and-forms-into-personal-xlsb.
However, I've inserted it into the attached workbook.
Hope that helps
For such sample
formula in E2 could be
=TEXTJOIN("",TRUE,
INDEX($B$2:$B$11,
MATCH(
MOD(MID(D2,ROW($A$1:INDEX($A$1:$A$18,LEN(D2))),1)-1,10)+1,
$A$2:$A$11,
0)
))
Thanks, but only first character is coming, I downloaded your file there also only first character is coming
That is array formula, you shall enter it with Ctrl+Shift+Enter instead of Enter.
Yes I did it in same manner, when I open your file it is read only where I can see it was correct the moment I click on edit the file it vanishes
Sorry. I missed this trick with N(IF1,..). Please check
=TEXTJOIN("",1, VLOOKUP(N(IF(1, --MID(D2,ROW($A$1:INDEX($A$1:$A$18,LEN(D2))),1) )), $A$2:$B$11,2,0 ) )