Convert Number To Letter

[mikec78]

I have an excel spreadsheet filled with numbers (i.e. a 10x10 excel sheet grid) and want to convert the numbers to letters. I want to map from 1-26 all the letters of the English alphabet. So 1=A, 2=B, 3=C, ...26=Z. I am a novice with excel and I know how to do some basic SUM formulas, but that is about it. Can someone point me in the right direction on how to convert this sheet or how to create a macro that can convert this sheet for me? I saw other posts that talk about converting letter grades to numbers then taking the average, etc, but I need to do the opposite. Any help? I am attaching a spreadsheet as an example that I am trying to convert from numbers to letters...

Thanks,
---MC

[RichardSchollar]

Hi Mike

Assume the number you want to convert is in A1 then the following formula will convert it to the corresponding letter:

=CHAR(64+A1)

Richard

ADDED BY ADMIN

 'Remove for 0=a
Option Base 1 '1=a
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim MyArray 
    Dim lloop As Long 
     
    MyArray = Array("a", "b", "c", "d", "e", "f", "g", _ 
    "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _ 
    "r", "s", "t", "u", "v", "w", "x", "y", "z") 
    With Target(1, 1) 
        If .Column = 3 Then 
            If IsNumeric(.Value) Then 
                If .Value >= LBound(MyArray) And .Value <= UBound(MyArray) Then 
                    Target(1, 1).NumberFormat = """" & UCase(MyArray(Target(1, 1))) & """" 
                End If 
            End If 
        End If 
    End With 
End Sub 

[mikec78]

I added a sample excel spreadsheet of what I am trying to convert. The grids could be 7x7, 10x10, 20x20, etc. So the letters could be in any cell and I wanted to know if there was a macro that I could run to change all the numbers to letters. Also the formula you gave me where would I put it? And how is it run? (I am a novice)
Thanks...

[Bob Phillips]

Sub NumbersToLetters() 
    Dim cell As Range 
     
    For Each cell In Range("A1").CurrentRegion 
         
        If cell.Value <= 26 Then _ 
        cell.Value = Chr(cell.Value + 64) 
    Next cell 
End Sub 

[mikec78]

Richard,

When I try that formula you suggest
=CHAR(64+A1)

I get the error:
#NAME?

When the number in cell A1 = 26 in the "sample_format.xls" that I attached. Any other suggestions? I think we might be getting close, but I am not sure because I need to convert an entire sheet...

Thanks...
---Mike

---

Auto Merged Post Until 24 Hrs Passes;

Bob,

I created the macro and your program works! I have one other question which I don't know how to add to your macro. Is there a way to have a number that is greater than 26 subtract 26 from the number until it gets in range between 1 and 26? Does that make sense? If I have a number in a cell that is 46, I want to do 46-26=20 then add 64 to it which correlates to the letter 84 = T. Is there a way to do something like that? <=26?

We are getting very close to solving the problem I have!!!
Everyone totally rocks on this site that helps out!!!


Thanks,
---Mike

---

Auto Merged Post Until 24 Hrs Passes;

Bob,

I created the macro and your program works! I have one other question which I don't know how to add to your macro. Is there a way to have a number that is greater than 26 subtract 26 from the number until it gets in range between 1 and 26? Does that make sense? If I have a number in a cell that is 46, I want to do 46-26=20 then add 64 to it which correlates to the letter 84 = T. Is there a way to do something like that? <=26?

We are getting very close to solving the problem I have!!!
Everyone totally rocks on this site that helps out!!!


Thanks,
---Mike

[norie]

Mike

How does 84 correlate to T?

I downloaded your file and was looking in to creating a solution, then I saw that.

[Bob Phillips]

Sub NumbersToLetters() 
    Dim cell As Range 
     
    For Each cell In Range("A1").CurrentRegion 
         
        If cell.Value Mod 26 > 0 And cell.Value Mod 26 < 26 Then _ 
         
        cell.Value = Chr(cell.Value Mod 26 + 64) 
    Else 
         
        cell.Value = "A" 
    End If 
Next cell 
End Sub 

---

Auto Merged Post Until 24 Hrs Passes;

Mike

How does 84 correlate to T?

I downloaded your file and was looking in to creating a solution, then I saw that.

?chr(84)

---

Auto Merged Post Until 24 Hrs Passes;

[mikec78]

I got it from this chart link that I found:
http://www.techonthenet.com/ascii/chart.php

I could be wrong though...
I am going to test out what you posted above...

Thanks for all this help Bob, I can't thank you enough...
Now to test out the new macro you wrote...

---

Auto Merged Post Until 24 Hrs Passes;

The new macro you wrote works like a dream!!!

Thanks for everything. (I wish I was smart enough to know how you did that)

I can't thank you enough...
---Mike

[Bob Phillips]

I got it from this link that I found:
http://www.techonthenet.com/ascii/chart.php

I could be wrong though...

Nope you are right, CHR(84) is T. Norie just didn't read what you wrote properly.

[norie]

Bob

Yes I didn't read the post properly but you've got to admit the whole thing is a bit confusing.