 # Thread: Convert Number to Letter of the Alphabet

1. ## Convert Number to Letter of the Alphabet

This function returns alphabet/alphabet-series-string from a specified number.
For example :
GetAlphaString(26) ---returns---> "Z"
GetAlphaString(27) ---returns---> "AA"
GetAlphaString(28) ---returns---> "AB"
and
GetAlphaString(18278) ---returns---> "ZZZ"

Drawback: This function cannot give a result if the given number is greater than 18278

It is as like in Excel columns. If the excel column number is 27, the column name will be "AA".

Hope this will be useful for anyone. Code:
```Public Function GetAlphaString(AlphaNum As Integer)
Dim alphaCD1 As String
Dim alphaCD2 As String
Dim DV() As String
Dim TV() As String
alphaCD1 = Round(Val((AlphaNum - 1) / 26), 3)
alphaCD2 = Round(Val((AlphaNum - 1) / 676), 3)
DV = Split(alphaCD1, ".")
TV = Split(alphaCD2, ".")

Select Case AlphaNum
Case 1 To 27 'A to Z
GetAlphaString = Chr\$(64 + AlphaNum)
Case 27 To 702 'AA to ZZ
GetAlphaString = Chr\$(64 + DV(0)) & Chr\$(64 + (AlphaNum - (26 * DV(0))))
Case 702 To 18278 'AAA to ZZZ
If TV(0) < 27 Then 'If does not exceeds the limit of 26 (total no. of english alphabets).
GetAlphaString = Chr\$(64 + TV(0)) & Chr\$(64 + (DV(0) - (26 * TV(0)))) & Chr\$(64 + (AlphaNum - (26 * DV(0))))
Else 'If exceeds the limit of 26 (total no. of english alphabets).
GetAlphaString = "Z" & "Z" & Chr\$(64 + (AlphaNum - (26 * DV(0))))
End If
Case Else
Exit Function
End Select
End Function```
Usage:
Code:
```Private Sub Command1_Click()
If Val(Text1.Text) > 18278 Then
MsgBox "Sorry! the limit exceeded.", vbInformation, "Error!"
Exit Sub
Else
MsgBox GetAlphaString(Text1.Text)
End If
End Sub```  Reply With Quote

2. ## Re: [RESOLVED] Convert number to letter of the alphabet

That appears to be purely VB6, not VB.NET. It will likely all work in .NET, but it uses exclusively legacy methods, as far as I can see. And the thread is still 11 years old.  Reply With Quote

3. ## Re: [RESOLVED] Convert number to letter of the alphabet

oh!...
i didn,t notice that ... 11 years...
Sorry! I thought this was a VB6 thread.   Reply With Quote

4. ## Re: [RESOLVED] Convert number to letter of the alphabet Originally Posted by VineethChandran This function returns alphabet/alphabet-series-string from a specified number.
For example :
GetAlphaString(26) ---returns---> "Z"
GetAlphaString(27) ---returns---> "AA"
GetAlphaString(28) ---returns---> "AB"
and
GetAlphaString(18278) ---returns---> "ZZZ"

Drawback: This function cannot give a result if the given number is greater than 18278

It is as like in Excel columns. If the excel column number is 27, the column name will be "AA".

Hope this will be useful for anyone. Code:
```Public Function GetAlphaString(AlphaNum As Integer)
Dim alphaCD1 As String
Dim alphaCD2 As String
Dim DV() As String
Dim TV() As String
alphaCD1 = Round(Val((AlphaNum - 1) / 26), 3)
alphaCD2 = Round(Val((AlphaNum - 1) / 676), 3)
DV = Split(alphaCD1, ".")
TV = Split(alphaCD2, ".")

Select Case AlphaNum
Case 1 To 27 'A to Z
GetAlphaString = Chr\$(64 + AlphaNum)
Case 27 To 702 'AA to ZZ
GetAlphaString = Chr\$(64 + DV(0)) & Chr\$(64 + (AlphaNum - (26 * DV(0))))
Case 702 To 18278 'AAA to ZZZ
If TV(0) < 27 Then 'If does not exceeds the limit of 26 (total no. of english alphabets).
GetAlphaString = Chr\$(64 + TV(0)) & Chr\$(64 + (DV(0) - (26 * TV(0)))) & Chr\$(64 + (AlphaNum - (26 * DV(0))))
Else 'If exceeds the limit of 26 (total no. of english alphabets).
GetAlphaString = "Z" & "Z" & Chr\$(64 + (AlphaNum - (26 * DV(0))))
End If
Case Else
Exit Function
End Select
End Function```
Usage:
Code:
```Private Sub Command1_Click()
If Val(Text1.Text) > 18278 Then
MsgBox "Sorry! the limit exceeded.", vbInformation, "Error!"
Exit Sub
Else
MsgBox GetAlphaString(Text1.Text)
End If
End Sub```
I'm using VB6 and this isn't working for me. For example 52 returns B@. Is this the case for you as well? If not can you post your Round() function?  Reply With Quote

5. ## Re: Convert Number to Letter of the Alphabet

This part of a thread was pruned from an old thread in the .NET forum. It was a VB6 solution to a .NET question, but it has a bit of life with the recent question, so it seems like it belongs somewhere, and this is more correct than .NET.  Reply With Quote

6. ## Re: Convert Number to Letter of the Alphabet

FOR ME (copying exact code), "52" in text1 produces AZ in the messagebox on Command1_click event.  Reply With Quote

7. ## Re: Convert Number to Letter of the Alphabet

Many have been confused (as was intended) by Microsoft's naming of The Great Pretender as "VB." This is exactly why people said "Visual Basic .Net" should have been named "Visual Fred" or something, anything else.

It is not VB. But this is a curse we must still live with... at least until they finally kill VB.Net off once and for all.

In any case that sure seems convoluted to me for something fairly simple. For example:

Code:
```Public Function GetAlphaString(ByVal AlphaNum As Long) As String
Do While AlphaNum > 0
AlphaNum = AlphaNum - 1
GetAlphaString = ChrW\$(65 + AlphaNum Mod 26) & GetAlphaString
AlphaNum = AlphaNum \ 26
Loop
End Function
:
:
Debug.Print "GetAlphaString(0) -> """; GetAlphaString(0); """"
Debug.Print "GetAlphaString(1) -> """; GetAlphaString(1); """"
Debug.Print "GetAlphaString(26) -> """; GetAlphaString(26); """"
Debug.Print "GetAlphaString(27) -> """; GetAlphaString(27); """"
Debug.Print "GetAlphaString(28) -> """; GetAlphaString(28); """"
Debug.Print "GetAlphaString(18278) -> """; GetAlphaString(18278); """"
Debug.Print "GetAlphaString(18279) -> """; GetAlphaString(18279); """"
Debug.Print "GetAlphaString(2147483647) -> """; GetAlphaString(2147483647); """"```
Results:

Code:
```GetAlphaString(0) -> ""
GetAlphaString(1) -> "A"
GetAlphaString(26) -> "Z"
GetAlphaString(27) -> "AA"
GetAlphaString(28) -> "AB"
GetAlphaString(18278) -> "ZZZ"
GetAlphaString(18279) -> "AAAA"
GetAlphaString(2147483647) -> "FXSHRXW"```  Reply With Quote

8. ## Re: Convert Number to Letter of the Alphabet Originally Posted by dilettante Many have been confused (as was intended) by Microsoft's naming of The Great Pretender as "VB." This is exactly why people said "Visual Basic .Net" should have been named "Visual Fred" or something, anything else.

It is not VB. But this is a curse we must still live with... at least until they finally kill VB.Net off once and for all.

In any case that sure seems convoluted to me for something fairly simple. For example:

Code:
```Public Function GetAlphaString(ByVal AlphaNum As Long) As String
Do While AlphaNum > 0
AlphaNum = AlphaNum - 1
GetAlphaString = ChrW\$(65 + AlphaNum Mod 26) & GetAlphaString
AlphaNum = AlphaNum \ 26
Loop
End Function
:
:
Debug.Print "GetAlphaString(0) -> """; GetAlphaString(0); """"
Debug.Print "GetAlphaString(1) -> """; GetAlphaString(1); """"
Debug.Print "GetAlphaString(26) -> """; GetAlphaString(26); """"
Debug.Print "GetAlphaString(27) -> """; GetAlphaString(27); """"
Debug.Print "GetAlphaString(28) -> """; GetAlphaString(28); """"
Debug.Print "GetAlphaString(18278) -> """; GetAlphaString(18278); """"
Debug.Print "GetAlphaString(18279) -> """; GetAlphaString(18279); """"
Debug.Print "GetAlphaString(2147483647) -> """; GetAlphaString(2147483647); """"```
Results:

Code:
```GetAlphaString(0) -> ""
GetAlphaString(1) -> "A"
GetAlphaString(26) -> "Z"
GetAlphaString(27) -> "AA"
GetAlphaString(28) -> "AB"
GetAlphaString(18278) -> "ZZZ"
GetAlphaString(18279) -> "AAAA"
GetAlphaString(2147483647) -> "FXSHRXW"```
I believe some one will ask "how to convert FXSHRXW to 2147483647"?   Reply With Quote

9. ## Re: Convert Number to Letter of the Alphabet Originally Posted by DaveDavis I believe some one will ask "how to convert FXSHRXW to 2147483647"? Code:
```ColumnLetter = "FXSHRXW"
ColumnLetterToNumber = 0
For i = 1 To Len(ColumnLetter)
ColumnLetterToNumber = ColumnLetterToNumber * 26 + (Asc(UCase(Mid(ColumnLetter, i, 1))) - 64)
Next
Debug.Print ColumnLetterToNumber```  Reply With Quote

10. ## Re: Convert Number to Letter of the Alphabet

this is the same logic when you convert a decimal to hexadecimal or binary or octal or any other non-10 base formula. this one is 26  Reply With Quote

11. ## Re: Convert Number to Letter of the Alphabet

@baka: Not quite. It's actually more akin to base-27, as there's an implicit zero as the 27th (or 1st) digit. Also, when going to alpha, all the letters are offset by 65 in the ASCII table, so that's another complication.

EDIT1: But I suppose any base conversion will have the complications of offsets in the ASCII table. So, it's just that we must appreciate that it's base-27, not base-26.  Reply With Quote

12. ## Re: Convert Number to Letter of the Alphabet

hm.
base decimal = 0,1,2,3,4,5,6,7,8,9 = 10
base letters = 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 = 26

where u get the 27th letter?
(just check the solutions, they all use 26 to divide or multiply. that suggest base-26 similar to decimal we use 10)  Reply With Quote

13. ## Re: Convert Number to Letter of the Alphabet

Ok, let's think of it like this. If we start counting at 1 and want to count off 10 spots for base-10, we'd do something like this:

1 2 3 4 5 6 7 8 9 10

We see that the zero gets introduced at the first double-digit (i.e., 10). However, if we really wanted to see it better, we might go:

01 02 03 04 05 06 07 08 09 10

And then, we see our leading zero.

Now, for Excel columns, they've also got that implicit zero. So, to start at 1, and count through the base, we'd start at column "A" (i.e., 1), and count through to column "AA" (the equivalent of 10 in base-10). Let's see what we get: We just can't forget about the implicit zero.

Another way to think about it is: Any base is defined by the number of digits it has. Base-10 has 0,1,2,3,4,5,6,7,8,9 (10 digits). Excel columns have 26 letters and an implicit zero (27 total digits).

Take Care,
Elroy  Reply With Quote

14. ## Re: Convert Number to Letter of the Alphabet

In fact, it's not even that straight-forward, because you skip over the equivalent of a "A0" column. So, bottom line, we really can't say that this is just a simple base conversion. It's just a somewhat convoluted thing that Microsoft came up with for Excel columns.

The posts in post #7 and #9 do a nice job of getting us back and forth though. I've used something similar for years, as I do a substantial amount of Excel automation.

Best Regards,
Elroy  Reply With Quote

15. ## Re: Convert Number to Letter of the Alphabet

not convinced. instead of A=1 i will use A=0 to make it more similar to decimal.

A=0 (like decimal 0), that means, B=1 up to Z=25, that means AA is 26 (or in decimal 10)
AB is the next base-set (like decimal 11)

just think it like this, if decimal has

0,1,2,3,4,5,6,7,8,9,10 (its base-10, but theres 11 numbers, if we follow your counting)
wheres 0 in your calculations? remember that 10 is 1 and 0, not 11, so 0 is there and used.
if u use 1,2,3,4,5,6,7,8,9, the next one need to be 11, not 10, as 0 is there and should not be used (like A)
so, if u use 1-10 and "discard 0" as the precedent, we need to do that as well with letters.
so 1 is actually B,

B,C,...Z,BA is equivalent to 1-10 (where A=0)

the confusing part of the letter base conversion, is that we assume A is 1 not 0
what is 0 and why not used again? so in my mind its a base-26, but we add 1
to simplify lets change "" to @ and we have:

0=@
1=A
2=B
3=C
26=Z
27=A@ ??
28=AA

but theres no A@. the base-27 is broken!?! so it most be base-26  Reply With Quote

16. ## Re: Convert Number to Letter of the Alphabet Originally Posted by baka A=0 (like decimal 0), that means, B=1 up to Z=25, that means AA is 26
Baka, with your thinking, wouldn't AA still be 0? EDIT1: Say Baka, rather than making yet another post on this, I'll just append to this one. I didn't mean to start a long discussion about this. I just felt obligated to point out that this isn't "exactly" a base-26 situation, as suggested in post #14. Take Care, Elroy  Reply With Quote

17. ## Re: Convert Number to Letter of the Alphabet

0=@
1=A
2=B
3=C
26=Z
27=A@ ??
28=AA

if its base-27, after Z it should be A@. but its AA.
the conversion assume A=1, but skip 0.
0 is only used once, after that never again. its like its a base infinite, theres no end to it.
but its a circle of 26 letters, and thats base-26, only the first set, has base-27 this because 0 is used.  Reply With Quote

18. ## Re: Convert Number to Letter of the Alphabet

You're looking at it wrong.

There is no zero. My code above produces an empty String for an input of 0 though by rights it should probably raise an exception. Natural numbers begin at 1, though some people like to include 0 so I didn't bother making it an error. In a "counting numbers" scheme zero is closer to Null (nothing at all, no items) than it is anything else.

You are trying to apply place-value notation with 26 symbol alphabet. That's not at all what this thread is about.

What we have here is something a bit closer to Roman Numerals than it is to conventional decimal notation.  Reply With Quote

19. ## Re: Convert Number to Letter of the Alphabet

no, im not, the logic is there, as i pointed it out. i didnt say its a new numeral mode we can use, still we can apply the same logic.
26 letters suggest 26 base and we can use that to figure out the formula, to figure out what a decimal number is in this 26-letters or vice versa.
im sure that your mind gave you this "input" when you wrote that code, as you use 26 as base to convert numbers to letters.
Elroy needed to change it to base-27, and there was when we started to argue about it, in absurdum almost.

this logic is helpful when we encounter problems like this. first where, if, theres a base in any equations/formulas.
this problem here, used the base- principles to create the functions you made. if this is wrong, than your mathematical mind works completely different than mine.  Reply With Quote

20. ## Re: Convert Number to Letter of the Alphabet

Well, ok, one more way to look at it. I still insist it's just a weird thing Microsoft cooked up, and doesn't really lend itself to mathematical bases. Let's look at it like this:

If we take two digits, using # as place-holders, we've got ## for our two digit number. So, starting at 1 (i.e., "A"), how many different digits (values, things, whatever) can we put into the second digit:

Code:
```   ##
^
|
That digit right there, the second one.```

If we're in base 10 (or pretty much any true base system), we know that it's either implicitly (as in 1) or explicitly (as in 01) a zero. However, what is it for the column #1 (the "A" column) in Excel?

The fact that there's no easy answer for that question tells us that we're in a funky counting system.

Ok, on that note, I'm out'a here.

Y'all Take Care,
Elroy  Reply With Quote

21. ## Re: Convert Number to Letter of the Alphabet Originally Posted by Elroy The fact that there's no easy answer for that question tells us that we're in a funky counting system.
It's not in any way "funky", but clearly derived from a base26 alphabet...
(the only difference is, that the Key/Value mappings in said alphabet are referring to one-based increasing Values, instead of the usual zerobased ones).

For Binary base2 we (usually) have Key/Value-pair-mappings of the Alphabet of:
"0"/0
"1"/1

For HexaDecimal base16 mappings we (usually) have an alphabet of Key/Value pairs of:
"0"/0
"1"/1
...
"E"/14
"F"/15

And for Excel the mappings also have increasing Values, but simply one-based ones:
"A"/1
"B"/2
...
"Y"/25
"Z"/26

The generic Formula for all that is a Linear-Combination - in our field usually calculated as an accumulating sum:
- in a loop from 0 to n-1
- over all chars of the input-string (which need to be elements of the given alphabet)
- starting with the least significant char-value
- what gets sumed-up then is simply: AlphaValue(i) * Alphabet.Count ^ i

Here's an example, which tries to express that with generic code (usable with different alphabets and bases):
Code:
```Function GetNumberFromBaseNotation(ByVal S\$, Alphabet As Collection)
S = StrReverse(UCase\$(S)) 'the least signifcant value is usually to the right, so we reverse the string
Dim i As Long, AV As Long
For i = 0 To Len(S) - 1
AV = Alphabet(Mid\$(S, i + 1, 1)) 'get the Value from the Alphabet-Char at input-index i
GetNumberFromBaseNotation = GetNumberFromBaseNotation + AV * Alphabet.Count ^ i
Next
End Function

Function GetAlphabetForBase(ByVal Base As Long) As Collection 'just a little helper for the different alphabets
Set GetAlphabetForBase = New Collection
Dim i As Long
Select Case Base
Case 2:  For i = 0 To 1:  GetAlphabetForBase.Add i, Chr\$(48 + i): Next
Case 10: For i = 0 To 9:  GetAlphabetForBase.Add i, Chr\$(48 + i): Next
Case 16: For i = 0 To 15: GetAlphabetForBase.Add i, Chr\$(IIf(i > 9, 55, 48) + i): Next
Case 26: For i = 1 To 26: GetAlphabetForBase.Add i, Chr\$(64 + i): Next  'note the one-based construction here
End Select
End Function```
Here's some test-code for the above functions (all printing out the same "decoded value" of 28)
Code:
```Option Explicit

Dim Alphabet As Collection

Set Alphabet = GetAlphabetForBase(2)
Debug.Print GetNumberFromBaseNotation("11100", Alphabet)

Set Alphabet = GetAlphabetForBase(10)
Debug.Print GetNumberFromBaseNotation("28", Alphabet)

Set Alphabet = GetAlphabetForBase(16)
Debug.Print GetNumberFromBaseNotation("1C", Alphabet)

Set Alphabet = GetAlphabetForBase(26)
Debug.Print GetNumberFromBaseNotation("AB", Alphabet)
End Sub```
HTH

Olaf  Reply With Quote

#### Posting Permissions

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

Featured