Aug 23 2018 02:15 PM
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?
Aug 23 2018 03:24 PM
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)
Aug 23 2018 05:20 PM - edited Aug 23 2018 05:59 PM
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
Aug 23 2018 09:14 PM - edited Aug 23 2018 09:22 PM
SolutionHi 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 link 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 link.
However, I've inserted it into the attached workbook.
Hope that helps
Aug 24 2018 06:33 AM
Haytham,
Thank you! This is precisely what I was looking for. I appreciate your help.
Regards,
Jason
Aug 24 2018 06:39 AM
This works wonderfully! Thank you for your help!
Aug 24 2018 06:40 AM
I was wondering about the numbers to letters -- I figured it was just like your suggestion, but I wasn't sure. Thank you!
Jan 17 2019 08:40 AM
You can use =CODE(UPPER(A1))-64
It will work for uppercase and lowercase.
Jan 20 2019 12:22 PM
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
Aug 09 2019 02:05 PM
@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!!!
Aug 09 2019 02:47 PM
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
Aug 09 2019 07:00 PM
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 Function
I 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
Aug 27 2019 12:11 AM - edited Aug 27 2019 12:50 AM
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
Aug 27 2019 02:15 AM
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)
))
Aug 27 2019 04:10 AM - edited Aug 27 2019 04:12 AM
Thanks, but only first character is coming, I downloaded your file there also only first character is coming
Aug 27 2019 04:25 AM
That is array formula, you shall enter it with Ctrl+Shift+Enter instead of Enter.
Aug 27 2019 04:51 AM - edited Aug 27 2019 04:55 AM
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
Aug 27 2019 05:59 AM
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
)
)
Aug 27 2019 06:34 AM
Great it is working ! but when I tried it in different cells I don't know why it is not working I changed all the reference cell addresses.. It is working only at A1
Aug 27 2019 08:08 AM
I sometimes use LOOKUP as an alternative to INDEX when coercion is required.
= CONCAT( LOOKUP( VALUE(MID(number,k,1)), digits, characters ) )
The reverse decoding and I do not have to convert text to numbers
= CONCAT( LOOKUP( MID(text, k, 1), array ) )
but the elephant in that room is that I have had to sort the lookup data
It is OK for me to use 'array'
= SORT( IF( {0,1}, digits, characters ) )
but most users would be better off with a manual sort using a helper range.