Convert number to letters in Excel Solved/Closed

Question

Hello,  

I have to define the numbers to their associated letters and based on the combination of numbers the next cell should show the letters.  

Example: 

1 = A 
2 = B 
3 = C 
4 = D.... 

If I enter number in A1 cell as 3214, in B1 cell I should get CBAD i.e. [C(3), B(2), A(1), D(4)] 

Can we do this with simple formula. I am not aware of VB and Macros. Please provide your suggestions 

Configuration: Windows XP / Internet Explorer 8.0

fairooz · Accepted Answer

If only upto the 9th Alphabet is needed then you can do the following:

Press Alt + F11, VB Window will appear
From the menu above Click "Insert" then "Module" then paste the below code:
---------------------------------------------------------------
Sub convertToAlphabets()

    Dim rng, TargetRange As Range
    Dim itemCount, cellLength As Long
    Dim TempStr As String
    
    itemCount = Range("A1").CurrentRegion.Rows.Count
    Set rng = Range("A:A")
    Set TargetRange = Range("B:B")
    
    For j = 2 To itemCount
        TempStr = ""
        cellLength = Len(rng.Cells(j, 1))
        For I = 1 To cellLength
            Select Case Mid(rng.Cells(j, 1), i, 1)
                Case 1: TempStr = TempStr + "A"
                Case 2: TempStr = TempStr + "B"
                Case 3: TempStr = TempStr + "C"
                Case 4: TempStr = TempStr + "D"
                Case 5: TempStr = TempStr + "E"
                Case 6: TempStr = TempStr + "F"
                Case 7: TempStr = TempStr + "G"
                Case 8: TempStr = TempStr + "H"
                Case 9: TempStr = TempStr + "I"
            End Select
        Next i
        TargetRange.Cells(j, 1) = TempStr
    Next j
End Sub
---------------------------------------------------------------
Then press "F5"

Note: Save your document first before running the code. It will replace all the cells in Column B and you can not undo this operation. Let me know if it worked

fairooz · Answer

I am assuming you need only upto "I"? Since its the letter number 9?

6789 = FGHI
1011 = ?
Is it "JK" or "A0AA"?

I don't think its possible with Formula though, it can be done by using VB.

milindvc · Answer

Hi Fairooz,

I need to consider 10 parametrs i.e. 1,2,3,4,5,6,7,8,9,0

This numeric value will be an 'Amount' in A1 and I need to decode it to alphabets in B1 as A,B,C,D,E,F,G,H,I,J respectively. 

So when I enter amount '1200' in A1 in B1 it should show 'ABJJ'.

In your above code, do I need to update the cell number for reference and target?

fairooz · Answer

I have just added another line in the code.

Sub convertToAlphabets() 

Dim rng, TargetRange As Range 
Dim itemCount, cellLength As Long 
Dim TempStr As String 

itemCount = Range("A1").CurrentRegion.Rows.Count 
Set rng = Range("A:A") 
Set TargetRange = Range("B:B") 

For j = 2 To itemCount 
TempStr = "" 
cellLength = Len(rng.Cells(j, 1)) 
For I = 1 To cellLength 
Select Case Mid(rng.Cells(j, 1), i, 1) 
Case 1: TempStr = TempStr + "A" 
Case 2: TempStr = TempStr + "B" 
Case 3: TempStr = TempStr + "C" 
Case 4: TempStr = TempStr + "D" 
Case 5: TempStr = TempStr + "E" 
Case 6: TempStr = TempStr + "F" 
Case 7: TempStr = TempStr + "G" 
Case 8: TempStr = TempStr + "H" 
Case 9: TempStr = TempStr + "I" 
Case 0: TempStr = TempStr + "J" 
End Select 
Next I 
TargetRange.Cells(j, 1) = TempStr 
Next j 
End Sub 

Now for A to J it will show the characters. It worked here. Not sure what the problem is, but you have to run the code every time you add a value in the first column. If you are updating the first column every time, then you can add a button. If you want to have the button let me know.

Mehedad · Answer

1. I am using Excel 2010 so here is how it goes to enable Macros:
File -> Options -> Trust Center (Left Menu) -> Trust Center Settings... (Button) -> Choose "Enable All Macros" -> Hit "OK"

2. I have modified the code a bit for only the Cell "L57" and the output will be on Cell "M57"

Sub convertToAlphabets()

    Dim rng, TargetRange As Range
    Dim itemCount, cellLength As Long
    Dim TempStr As String

    Set rng = Range("L57")
    Set TargetRange = Range("M57")
    TempStr = ""
    cellLength = Len(rng)
    For I = 1 To cellLength
            Select Case Mid(rng.Value, i, 1)
            Case 1: TempStr = TempStr + "A"
            Case 2: TempStr = TempStr + "B"
            Case 3: TempStr = TempStr + "C"
            Case 4: TempStr = TempStr + "D"
            Case 5: TempStr = TempStr + "E"
            Case 6: TempStr = TempStr + "F"
            Case 7: TempStr = TempStr + "G"
            Case 8: TempStr = TempStr + "H"
            Case 9: TempStr = TempStr + "I"
            Case 0: TempStr = TempStr + "J"
            End Select
     Next i
     TargetRange.Value = TempStr
End Sub

3. The code can be further modified for all the sheets if the the Cell Location is the same that is "L57". If the Cell Locations are different in other sheets, I will have to look into that in my PC creating a different model. The fastest answer can come from Venkat or rizVisa1 in this forum. Hope they will see this thread.
Till then I will try to find a solution. I am still new at this Excel thing and learning.

btw, Mehedad is my last name.

Mehedad · Answer

Two lines are changed so it rounds up the number in the code also. It worked here.

Sub convertToAlphabets()

    Dim rng, TargetRange As Range
    Dim itemCount, cellLength As Long
    Dim TempStr As String
        
    Set rng = Range("A2")
    Set TargetRange = Range("B2")
    
    TempStr = ""
    cellLength = Len(Round(rng.Value,0))
        For I = 1 To cellLength
            Select Case Mid(Round(rng.Value, 0), i, 1)
            Case 1: TempStr = TempStr + "A"
            Case 2: TempStr = TempStr + "B"
            Case 3: TempStr = TempStr + "C"
            Case 4: TempStr = TempStr + "D"
            Case 5: TempStr = TempStr + "E"
            Case 6: TempStr = TempStr + "F"
            Case 7: TempStr = TempStr + "G"
            Case 8: TempStr = TempStr + "H"
            Case 9: TempStr = TempStr + "I"
            Case 0: TempStr = TempStr + "J"
            End Select
        Next i
        TargetRange.Value = TempStr
End Sub

Mehedad · Answer

Sorry was out for lunch....

Sub convertToAlphabets()

    Dim rng, TargetRange As Range
    Dim itemCountTemp, cellLength As Long
    Dim TempStr As String
    
    itemCountTemp = 33
    
     Set rng = Range("L56:L88")
     Set TargetRange = Range("M56:M88")

        For j = 1 To itemCountTemp
            TempStr = ""
            cellLength = Len(Round(rng.Cells(j, 1), 0))
            If rng.Cells(j, 1) <> "" Then
                For I = 1 To cellLength
                    Select Case Mid(Round(rng.Cells(j, 1), 0), I, 1)
                        Case 1: TempStr = TempStr + "A"
                        Case 2: TempStr = TempStr + "B"
                        Case 3: TempStr = TempStr + "C"
                        Case 4: TempStr = TempStr + "D"
                        Case 5: TempStr = TempStr + "E"
                        Case 6: TempStr = TempStr + "F"
                        Case 7: TempStr = TempStr + "G"
                        Case 8: TempStr = TempStr + "H"
                        Case 9: TempStr = TempStr + "I"
                        Case 0: TempStr = TempStr + "J"
                    End Select
                Next I
                TargetRange.Cells(j, 1) = TempStr
            End If
        Next j

End Sub

There can be a shortcut key to run a macro which will be defined by you, but even then you need to set the range everytime. 

An advanced user, however, can make codes like that where you will be prompted to enter the range and it will operate based on your input.

Also check out the "itemCountTemp = 33". I just put the length of range manually, that also has to change. 

If all the sheets had the data from "L56" to "L88", it would have been easier. I just made a code that can do the same operation in all the sheets (in one go) if the reference Cells were the same.

Btw, the blank Cells are not decoded now. 

I wish I could do better :(

Nermin · Answer

What will be the code if I want to convert back from letters to numbers e.g. from EJJC to 5003

Aridian · Answer

hey guys, I might look like a big goof but I came here to know how do u change numbers to letters in excel. I found all these ways quite complicated. and finally I used ctrl+f button and there is REPLACE option. I replaced the numbers with letters. I am not a computer person so dont laugh at me please. My comment could really be helpful for any other guy like me.

hachiya · Answer

Hi-

If you'd like to do this without macros, use the CHAR formula
this will convert the number to a letter.
For example =CHAR(65)  will give you A, =CHAR(66) will give you B and so on.
To get lowercase letters start at 97.
=CODE("A") will convert the letter back to the number 65.
Cheers,
H

Convert number to letters in Excel

10 responses

Similar discussions

Subscribe To Our Newsletter!