Forum Discussion
Excel -- Converting letters to number
Hi, all. Hoping I could find some help: I need to convert a string of letters into numbers, and vice versa. The set-up: the numbers 1,2,3,4,5,6,7,8,9,0 correspond to letters a,b,c,d,e,f,g,h,i,j. Example: If a user enters hajg in the cell, the formula (or macro) produces 8107. Or dibe produces 4925.
I can have two formulas, so the conversion doesn't have to go both ways in one formula. I just need to do both.
Any ideas out there?
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
35 Replies
By the way, formulas could be
text=>number
=--TEXTJOIN("",TRUE,MOD(CODE(LOWER(MID(A1,ROW($A$1:INDEX($A$1:$A$8,LEN(A1))),1)))-96,10))number=>text
=TEXTJOIN("",TRUE,CHAR( MOD(MID(A2,ROW($A$1:INDEX($A$1:$A$8,LEN(A2))),1)-1,10)+1+96))CSE (array) formula for pre-DA Excel
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 Jason,
I suggest you use https://zetexcel.com/. It has the ability to generate, modify, convert, render and print spreadsheets without using Microsoft Excel
You can use =CODE(UPPER(A1))-64
It will work for uppercase and lowercase.
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
I realize this thread is a bit old but..
I'm getting an error message with Haytham's formula any time my string of letters is greater than 5. I'm basically trying to create ID numbers for employees based on their first and last names.
James converts to 11451 -> correct for the code as written.
George grants an error message
any idea how to fix?Haytham Amairah this is brilliant! However, I couldn't figure out how to do numbers to letters, it show show #VALUE!...
Do you think you could shine some light?
Thank you so much!!!To do that, you need to change the above function as follows:
Function LettersToNumbers(text As String) As String Dim i As Integer Dim result As String For i = 1 To Len(text) Select Case Mid(text, i, 1) Case 1 result = result & "a" Case 2 result = result & "b" Case 3 result = result & "c" Case 4 result = result & "d" Case 5 result = result & "e" Case 6 result = result & "f" Case 7 result = result & "g" Case 8 result = result & "h" Case 9 result = result & "i" Case 0 result = result & "j" End Select Next LettersToNumbers = result End FunctionI guess that you have changed it like this but you forgot to change the function's return value data type from Integer to String, therefore, you got the #VALUE! error.
Regards
Mr. Lewin
nice formula!
then numbers to letters: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"a"),2,"b"),3,"c"),4,"d"),5,"e"),6,"f"),7,"g"),8,"h"),9,"i"),0,"j")
to extend - add equivalent numbers of "SUBSTITUTE(" to the formula
i.e. there are 10 substitutions - therefore 10 "SUBSTITUTE("
hope I am right... thanks
- HI all. Hoping I could find some help: I need to convert a string of letters into numbers.
B=1,A=2,E=3,D=4,L=5,C=6,Z=7,Y=8,Z=9,X=0.
When I input a cell in excel sheet BAED Then result show 1234.
For example
BDCX 1460
BAED 1234
BBBB 1111
XZCL 0965
pls helpWhy do you have Z=7 and Z=9?
I was wondering about the numbers to letters -- I figured it was just like your suggestion, but I wasn't sure. Thank you!
Hello Jason
Just a few nested SUBSTITUTE().
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",1),"b",2),"c",3),"d",4),"e",5),"f",6),"g",7),"h",8),"i",9),"j",0)
This works wonderfully! Thank you for your help!
