SOLVED

New Contributor

# 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?

35 Replies

# Re: Excel -- Converting letters to number

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)`

# RE: Excel -- Converting letters to number

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

best response confirmed by Jason Summer (New Contributor)
Solution

# Re: 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 TextFunction 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`

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

# Re: Excel -- Converting letters to number

Haytham,

Thank you!  This is precisely what I was looking for.  I appreciate your help.

Regards,

Jason

# Re: Excel -- Converting letters to number

This works wonderfully!  Thank you for your help!

# Re: RE: Excel -- Converting letters to number

I was wondering about the numbers to letters -- I figured it was just like your suggestion, but I wasn't sure.  Thank you!

# Re: Excel -- Converting letters to number

You can use =CODE(UPPER(A1))-64

It will work for uppercase and lowercase.

# Re: Excel -- Converting letters to number

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

# Re: Excel -- Converting letters to number

@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!!!

# Re: Excel -- Converting letters to number

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

# Re: Excel -- Converting letters to number

@jojog

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

# Re: Excel -- Converting letters to number

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

# Re: Excel -- Converting letters to number

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)
))``````

# Re: Excel -- Converting letters to number

Thanks, but only first character is coming, I downloaded your file there also only first character is coming

# Re: Excel -- Converting letters to number

That is array formula, you shall enter it with Ctrl+Shift+Enter instead of Enter.

# Re: Excel -- Converting letters to number

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

# Re: Excel -- Converting letters to number

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
)
)``````

# Re: Excel -- Converting letters to number

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

# Re: Excel -- Converting letters to number

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.