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
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 Function
Please 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
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
- INPUT INPUT 1 INPUT 2 INPUT 3 OUTPUT
D T 1
N 2
M 3
R 4
L 5
J G SH CH 6
K C Q 7
F V TH 8
P B 9
Z S (S sounding) C 0
can you share vba for above, Thanks INPUT INPUT 1 INPUT 2 INPUT 3 OUTPUT D T 1 N 2 M 3 R 4 L 5 J G SH CH 6 K C Q F V TH 8 P B 9 Z S 0 Try this:
Function LettersToNumbers(text As String) As String Dim i As Long Dim result As String For i = 1 To Len(text) Select Case UCase(Mid(text, i, 1)) Case "D" result = result & 1 Case "T" If UCase(Mid(text, i + 1, 1)) = "H" Then result = result & 8 i = i + 1 Else result = result & 1 End If Case "N" result = result & 2 Case "M" result = result & 3 Case "R" result = result & 4 Case "L" result = result & 5 Case "C" If UCase(Mid(text, i + 1, 1)) = "H" Then result = result & 6 i = i + 1 Else result = result & 7 End If Case "S" If UCase(Mid(text, i + 1, 1)) = "H" Then result = result & 6 i = i + 1 Else result = result & 0 End If Case "J", "G" result = result & 6 Case "K", "Q" result = result & 7 Case "F", "V" result = result & 8 Case "P", "B" result = result & 9 Case "Z" result = result & 0 End Select Next i LettersToNumbers = result End Function
