Convert number to letters in Excel [Solved/Closed]

milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 12, 2012 at 02:36 AM - Latest reply: hachiya 1 Posts Friday May 27, 2016Registration date May 27, 2016 Last seen
- May 27, 2016 at 11:11 AM
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

See more 

25 replies

Best answer
fairooz 11 Posts Thursday May 27, 2010Registration date September 12, 2012 Last seen - Sep 12, 2012 at 05:25 AM
6
Thank you
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

Thank you, fairooz 6

Something to say? Add comment

CCM has helped 1682 users this month

fairooz 11 Posts Thursday May 27, 2010Registration date September 12, 2012 Last seen - Sep 12, 2012 at 10:20 PM
5
Thank you
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.
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 02:32 AM
Hi Fairooz,

Yup, It now worked for me too on a new excel sheet. But while doing this on a sheet where I need to do this I am facing below problems:

1. 'The macros in this project are disabled' - How do I enable macros?

2. I only need this decoding for 1 cell. Reference for this decoding will be also from only 1 cell. E.g. Reference cell will be only L57; and cell to decode this will be only M57 (I may not need button as I can run it from Macro. But if button is to provide, where should we provide this?)

3. I would have to change this referencing for different sheets in the same file, other rules remain same.

Please suggest. I appreciate your efforts in providing this solution to me.

Rgds
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 03:21 AM
It is actually preferred if I can give the button for this calculation there instead of going macro and running it.
fairooz 11 Posts Thursday May 27, 2010Registration date September 12, 2012 Last seen - Sep 12, 2012 at 04:54 AM
1
Thank you
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.
Mehedad 22 Posts Thursday April 19, 2012Registration date April 16, 2013 Last seen - Sep 13, 2012 at 06:11 AM
1
Thank you
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 :(
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 07:21 AM
Hi Mehedad,

Thanks for a great help. Could you please tell me if I change these alphabets with other alphabets, do I need to make changes elsewhere in code? Many places it is mentioned 'j' and 'Next I' so got this doubt. I am not from a development background so have to ask such questions. :(
Mehedad 22 Posts Thursday April 19, 2012Registration date April 16, 2013 Last seen - Sep 13, 2012 at 07:24 AM
If you want to change the alphabets, change those that are followed by "Case x: " statements, others are variables and are used for computation only.
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 07:29 AM
Hi Mehedad,

Another challenge, if I enter some value in the reference cell and run macro I am able to get the decoding in another cell. Also, the black cells are not decoded now.

But if I have run the macro and got the decode. And then I delete the value and run macro again, it should remove the decode. However, it is keeping that decode still.

I am sorry to bother you so much and highly appreciate your efforts in helping me achieve this.
Mehedad 22 Posts Thursday April 19, 2012Registration date April 16, 2013 Last seen - Sep 13, 2012 at 07:47 AM
Sorry about that, add another line:
Else: TargetRange.Cells(j, 1) = ""
Just before the Line "End If"
1
Thank you
What will be the code if I want to convert back from letters to numbers e.g. from EJJC to 5003
1
Thank you
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 1 Posts Friday May 27, 2016Registration date May 27, 2016 Last seen - May 27, 2016 at 11:11 AM
1
Thank you
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
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 12, 2012 at 08:19 AM
0
Thank you
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?
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 12, 2012 at 08:27 AM
I tried the above solution you have given for 9 parameters upto 'I'.

Unfortunitely, it didn't workout.
Mehedad 22 Posts Thursday April 19, 2012Registration date April 16, 2013 Last seen - Sep 13, 2012 at 02:57 AM
0
Thank you
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.
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 03:14 AM
Hi Mehedad,

It worked for cell M57 but only when the reference cell L57 is directly entered number(s).

If I put formula in cell L57 (i.e. Reference cell) such as 12334.23*3.56 = 43910 (no decimals). It should decode to 5 letter alphabets, but it is decoding to all 10 letter alphabet. Try it on your system and let me know.

Appreciate efforts from you guys...
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 03:17 AM
This is because I am rounding off to no decimals. However the decoding cell is considering the actual calculation with decimals also.
Mehedad 22 Posts Thursday April 19, 2012Registration date April 16, 2013 Last seen - Sep 13, 2012 at 03:31 AM
0
Thank you
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
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 04:31 AM
Hi Mehedad,

We are just on the verge of closing this...

I want to give a reference cell range of L56:L88 and decode to M56:M88 respectively.

I need to give this referencing separate for each worksheet in one excel file. Also let me know how to do this.

If possible, I would like to give one button to run macro so that don't have to make 4 extra clicks to run macros, or is there any hot key for running macro?

This forum rocks guys... Thanks for your help...
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 04:39 AM
Hi Mehedad,

Another challenge is that if the cell is empty the decoding cell considers it to be with value '0' and decodes to respective alphabet. There should be no decoding for blank cells.
milindvc 12 Posts Wednesday September 12, 2012Registration date September 13, 2012 Last seen - Sep 13, 2012 at 08:19 AM
Great Help guys, I got this done.

Can I not copy this code for each worksheet in VB, instead of inserting a module. There is a blank page when you click on the Sheet 1, Sheet 2, Sheet 3. I guess these are to associate their VB codes. I tried this and it works as same.

Sadly, I cannot give one macro shortcut key to all the sheets (1,2 and 3); have to assign shortcut keys separately. May be giving a button can solve this issue as we don't have to remember shortcuts for each sheet separately.

By the way thanks for the help.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Sep 13, 2012 at 07:25 PM
Why you would need the code to each sheet ? From what I got from thread ( I confess I did not read all), it seems all you want to run is same macro irrespective of what sheet you are on and would like macro to act on that sheet

Other thing is that you can have a formula too. However that formula needs to be custom formula. It is same as a macro other than it would return a value back

Lets say that on you are entering number in cell A2
and you want answer to be in cell E5
then in cell E5 write
=convertToAlphabets(A2)

Public Function convertToAlphabets(sourceRange As Range) As String
    
    Dim TempStr             As String
    Dim cellLength          As Integer
    Dim I                   As Integer
    
    sourceValue = Trim(sourceRange.Value)
    If (sourceValue = vbNullString) Then
        resultString = vbNullString
    Else
        cellLength = Len(sourceValue)
        For I = 1 To cellLength
            Select Case Mid(sourceValue, 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
    End If
    
    convertToAlphabets = TempStr
    
End Function

-
Orangey > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 26, 2016 at 02:50 AM
Thanks for putting this up, it is very useful.

How could I change it so it includes trailing zeroes?

For example, 40.50 would be DJEJ

67.35 would be FGCE

and 15.00 would be AEJJ

I've had a hunt around the MSDN library, but I can't seem to get it to include trailing zeroes.