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
Nice, can you modify this
=TEXTJOIN("",TRUE,CHAR( MOD(MID(A2,ROW($A$1:INDEX($A$1:$A$8,LEN(A2))),1)-1,10)+1+96))if I want output in define set of letters in a given name range "mytable"
eg : cornflakes c for 1, o for 2 and so on..
input : 123 output : cor
mytable
| input | output |
| 1 | c |
| 2 | o |
| 3 | r |
| 4 | n |
| 5 | f |
| 6 | l |
| 7 | a |
| 8 | k |
| 9 | e |
| 0 | s |
this is the one I am using but how to make it reverse
https://exceljet.net/formula/translate-letters-to-numbers i.e numbers to letters
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)
))- Hello, I was wondering if you could help me. If I wanted to do this with letters to different letters what would I need to change to make this work? Thank you.
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
