Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Number To Letter

1. I agreed to these rules
Join Date
13th May 2008
Posts
4

## Convert Number To Letter

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

Excel Video Tutorials / Excel Dashboards Reports

2. Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Convert Number To Letter

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

VB:
``` '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

```
Last edited by Dave Hawley; May 14th, 2008 at 17:37.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
13th May 2008
Posts
4

## Re: Convert Number To Letter

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...

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Convert Number To Letter

VB:
```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

```

5. I agreed to these rules
Join Date
13th May 2008
Posts
4

## Re: Convert Number To Letter

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
Last edited by mikec78; May 14th, 2008 at 01:00. Reason: Auto Merged Doublepost

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Convert Number To Letter

Mike

How does 84 correlate to T?

7. ## Re: Convert Number To Letter

VB:
```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;

Originally Posted by norie
Mike

How does 84 correlate to T?

?chr(84)
Auto Merged Post Until 24 Hrs Passes;

Last edited by Bob Phillips; May 14th, 2008 at 03:01. Reason: Auto Merged Doublepost

8. I agreed to these rules
Join Date
13th May 2008
Posts
4

## Re: Convert Number To Letter

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
Last edited by Dave Hawley; May 14th, 2008 at 09:54.

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Convert Number To Letter

Originally Posted by mikec78
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.

10. ## Re: Convert Number To Letter

Bob

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno