Convert Numbers to Base-26 Letters
Greg Reed
Hi all!
I'm trying to write a function that will convert base-10 numbers to letters
(for the purpose of sequential numbering -- er, lettering).
Example:
A
.
.
Z
AA
AB
.
.
AZ
BA
.
.
You get the idea.
Here's what I have so far:
Public Function Num2LetterUCase(ByVal N As Integer) As String
'on error resume next
Const Digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intDigit As Integer
Dim strTemp As String
Do While N >= 26
intDigit = N Mod 26
strTemp = Mid(Digits, intDigit, 1) & strTemp 'Line 8
N = (N \ 26)
Loop
strTemp = Mid(Digits, N + 1, 1) & strTemp 'Line 11
Num2LetterUCase = strTemp
End Function
The problem is in dealing with zeros. As the above is written, I get an
error when I hit 26, because 26 mod 26 is 0 and I can't retrieve character
number zero from my string.
If I change lines 8 and 11 to read strTemp = Mid(Digits, intDigit + 1, 1) &
strTemp (which has the effect of making 0 = "A" and shifting all the other
characters down appropriately) then my lettering skips from 25="Z" to
26="BA" instead of 26="AA." While I didn't anticipate this effect, I
understand it, because "B" now takes on the same function as "1" in base-10.
What I need is a way for "A" to still represent "1" but to deal with those
numbers that are evenly divisible by 26 (and which are generating my
errors). To be honest, I don't really care whether the function begins
counting with "A" = 0 or "A" = 1. I can make it work either way.
Perhaps my lack of sleep is being a serious hindrance to my thinking
ability. (I just switched to the midnight shift at my day job. It'll take
me a couple of days to get adjusted to it.) At any rate, can anybody here
help me jump past the brain blockage that's preventing me from getting past
this seemingly simple conversion function?
Many thanks in advance,
- Greg Reed
--
e-mail is devremove...@dataspire.com
-----= Posted via Newsfeed.Com, Uncensored Usenet News =-----
http://www.newsfeed.com - The #1 Newsgroup Service in the World!
-----== 100,000 Groups! - 19 Servers! - Unlimited Download! =-----
R.Wieser
Greg Reed <inet...@hotmail.com> schreef in berichtnieuws
3fa3e562$1...@post.newsfeed.com...
Hello Greg,
> I'm trying to write a function that will convert base-10 numbers to
letters
> (for the purpose of sequential numbering -- er, lettering).
[Snip]
> 26="BA" instead of 26="AA." While I didn't anticipate this
> effect, I understand it, because "B" now takes on the same
> function as "1" in base-10.
>
> What I need is a way for "A" to still represent "1" but to deal
> with those numbers that are evenly divisible by 26 (and which
> are generating my errors).
And with plenty of other numbers. Try, for example, to write-out the number
678 .... If you use "A" as representing 0, you get "BAC" (26^2 *1 + 26^1 *0
+ 26^0 *2). If you however define "A" as 1, you get "A?B", because you
can't represent the middle 26^1 *0 ...
> To be honest, I don't really care whether the function begins
> counting with "A" = 0 or "A" = 1. I can make it work either way.
The problem is that "A" either represents the value 0, or it represents 1.
In the first case "AA" means 0, and in the second case "AA" means 1*26+1,
and you can't represent any number that contains a Zero in the resulting
number.
It allso means that, if you would still use all the letters, you went from a
base 26 to a base 27 -number (26 letters, with Zero undefined : 27 different
values in total)
There is just one, simple (but probably not acceptable to you) answer for it
: define another symbol as representing a Zero (a "." or "-" comes to mind)
Actually, you've got the same problems the old Romans had, using numbers
like XVI (16), as they did not have a symbol representing the Zero. They
needed ever more symbols (I=1, V=5, X=10, etc) for larger numbers. Ever
tried to write the size of your current drive in roman nummerals ? :-)
Regards,
Rudy Wieser
Bill Plenge
"Greg Reed" <inet...@hotmail.com> wrote in message
news:3fa3e562$1...@post.newsfeed.com...
The following should work, using A for 0.
Public Function Num2LetterUCase(ByVal N As Integer) As String
Dim Temp$
If N Then
While N
Temp$ = Temp$ & Chr$(Asc("A") + N Mod 26)
N = N \ 26
Wend
Else
Temp$ = "A"
End If
Num2LetterUCase = Temp$
End Function
I hope this helps.
Best,
Bill
Rick Rothstein
letters
> (for the purpose of sequential numbering -- er, lettering).
Give these two functions a try... ToAlpha take a base-10 number and converts
it to letters and ToNumber does the reverse.
Rick - MVP
Function ToAlpha(ByVal Value As Variant) As String
Do While Value
ToAlpha = Chr$((64 + (Value Mod 26))) & ToAlpha
Value = Value \ 26
Loop
End Function
Function ToNumber(Value As String) As Long
Dim X As Integer
For X = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, X, 1))) - 64) * _
26 ^ (Len(Value) - X)
Next
End Function
Rick Rothstein
functions listed below will count like this
A = 1
B = 2
...
...
Z = 26
AA = 27
...
...
AZ = 52
BA = 53
etc.
Both routines can handle numerical values up to, and including,
9999999999999999; and alphabetic codes up, and including, BRUTMHYHIIZO.
Those should cover the requirements of most people needing such
functions<g>. If the values submitted exceed these values, then the ToNumber
function will return -1 whereas the ToAlpha function will return "###".
Rick - MVP
Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function
Function ToNumber(Value As String) As Variant
Dim x As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For x = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, x, 1))) - 64) * _
26 ^ (Len(Value) - x)
Next
End If
End Function
Lizerd
A=1
B=2
....
Z=0
Will that work??? Everyone else looked like they missed it.....
Liz...
--
(All advice is checked, re-checked and verified to be questionable....)
"Greg Reed" <inet...@hotmail.com> wrote in message
news:3fa3e562$1...@post.newsfeed.com...
Rick Rothstein
> A=1
> B=2
> ....
> Z=0
>
> Will that work??? Everyone else looked like they missed it.....
Did you try the code I posted (the 2nd posting)? It does exactly what the OP
asked for... it counts by letters in exactly the way he indicated he
wanted... and it does so up to a limit far higher than a normal program
should ever have to deal with. Give it a try.
Rick - MVP
Lizerd
then converting the result to base26, instead of counting
base 26.
But I think either would work. Just depends on how OP
wants to do it.....
--
(All advice is checked, re-checked and verified to be questionable....)
"Rick Rothstein" <rickNOS...@NOSPAMcomcast.net> wrote in message
news:d9GdnU7BOsb...@comcast.com...
Rick Rothstein
thing. I know the OP **said** Base26, but he doesn't seem to actually
**mean** Base26. Just like Hex digits are 1234567890ABCDEF, Base26 digits
would have to be 1234567890ABCDEFGHIJKLMNOP; but the OP never mentions
outputting anything but the letters A thru Z. I took his post to mean he
wanted to "count" with the 26 letters of the alphabet... which is what the
code I posted does. I may be wrong, but I believe that is all the OP was
after.
Rick - MVP
"Lizerd" <1...@2.com> wrote in message
news:S1fpb.25048$Ec1.2...@bgtnsc05-news.ops.worldnet.att.net...
Lizerd
----
Both routines can handle numerical values up to, and including,
9999999999999999; and alphabetic codes up, and including, BRUTMHYHIIZO.
Why did your code stop at "BRUTMHYHIIZO"??
I just thought you were counting decimal, then converting to Base26...( Like
a human would)
Instead of counting in Base26.. (Like some strange E.T. would....)
AHhhh.... If the value was a string, then you could count from "Z" to
"YYY,YYY,YYY,YYY" or
higher...
( Yes??) :)
--
(All advice is checked, re-checked and verified to be questionable....)
(Maybe...)
"Rick Rothstein" <rickNOS...@NOSPAMcomcast.net> wrote in message
news:acydnXkV-aA...@comcast.com...
Rick Rothstein
> ----
> Both routines can handle numerical values up to, and
> including, 9999999999999999; and alphabetic codes
> up, and including, BRUTMHYHIIZO.
> ----
> Why did your code stop at "BRUTMHYHIIZO"??
I don't remember at the moment... I think it seemed like a convenient, human
readable number instead of using the absolute maximum that a Variant with a
sub-type of Decimal could handle.
> I just thought you were counting decimal, then
> converting to Base26...( Like a human would)
Well, sort of... remember, there is *no* Base26 being used anywhere (no
numbers used, only letters). Actually, I wasn't counting decimal per se;
rather, I was counting Modulo 26. So, where is my Mod function? This
expression
Number - Modulus * Int(Number / Modulus)
is exactly equivalent to
Number Mod Modulus
I used the expression I did because it can work with numbers larger than a
Long. Anyway, counting by Modulo 26 and then converting the number to
letters is the same as counting by letters directly (which is what I assumed
the OP wanted).
Rick - MVP
Greg Reed
<snip>
> Well, sort of... remember, there is *no* Base26 being used anywhere (no
> numbers used, only letters). Actually, I wasn't counting decimal per se;
> rather, I was counting Modulo 26. So, where is my Mod function? This
> expression
>
> Number - Modulus * Int(Number / Modulus)
>
> is exactly equivalent to
>
> Number Mod Modulus
>
> I used the expression I did because it can work with numbers larger than a
> Long. Anyway, counting by Modulo 26 and then converting the number to
> letters is the same as counting by letters directly (which is what I assumed
> the OP wanted).
>
>
> Rick - MVP
Sorry to post and then disappear, but the free news servers I've been
using for the past forever just stopped working. Not sure why, but
here I am on Google Groups (I hate using groups from a web browser) so
that I can read and post again.
First of all, thanks to everybody for their input. Yes, Rick is
correct about what I was looking for. I did finally manage to get my
code to do what I wanted it to do. I'll post it here:
<code>
Public Function Num2LetterUCase(ByVal N As Integer) As String
'on error resume next
Const Digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intDigit As Integer
Dim strTemp As String
Do While N >= 26
intDigit = N Mod 26
strTemp = Mid(Digits, intDigit + 1, 1) & strTemp
N = (N \ 26) - 1
Loop
strTemp = Mid(Digits, N + 1, 1) & strTemp
Num2LetterUCase = strTemp
End Function
</code>
I really like the two functions that Rick posted back on the 1st
["ToAlpha()" and "ToNumber()"]. I will certainly give them a try, and
just might use them instead of the one I posted above.
Again, thanks to all!
- Greg Reed
real e-mail is dev <at> dataspire <dot> com
J French
>"Rick Rothstein" <rickNOS...@NOSPAMcomcast.net> wrote in message news:<p-ydnd5nzoO...@comcast.com>...
>
><snip>
<snip>
>
>I really like the two functions that Rick posted back on the 1st
>["ToAlpha()" and "ToNumber()"]. I will certainly give them a try, and
>just might use them instead of the one I posted above.
>
<snip>
I wrote one of those things about 14 years ago
- it was for 'securing' our software - that we rented
- we would give people release keys that enabled the software for say
6 months - at that time it was mainly done over the 'phone
Since then I've learnt a few tricks
It does not have to go ABCDEF ....
Also one can remove potential problems, things that sound the same,
there is always 12345 ...