Converting base-10 numbers into base-26 letters

Question

I'm writing something that basically takes a bunch of files and renames them into a nice format, such as pic_000.jpeg, pic_001.jpeg, etc. One of the options is to write the incrementation as letters, e.g. pic_aaa.jpeg, pic_aab.jpeg, etc. Also, is they are converting a set of pic_000s into a set of pic_aaas, then there's also an option to preserve the order of increments. In other words, if you deleted pic_000 through pic_117, then the pic_aaa set would start at pic_aen, not pic_aaa. It's a little weird, but I'm just trying to give the user lot of options so they can do whatever they want.

I suppose there are two different types of output for a base-10 to base-26 function such as this:

1. without a fixed-length result, e.g.: A, B, ... , Z, AA, AB, ... ZZ, AAA (notice that A is representing a 1 when it is the leading letter and the result is more than one letter in length, else it is representing a 0)

2. with a fixed-length result, e.g. AAA, AAB, ... , ZZY, ZZZ (A will always represent a zero, here)

Again, I've chosen to write it both ways and let the user decide what they want. With (2), you just have to count how many input numbers there are (the numbers that we're converting in the first place) and then use an appropriately-sized result. Obviously two characters (AA, AB, etc.) isn't enough to represent a million different numbers, so you'd have see if three letters could do it, then four, and so on. (To make this easier, you can just do resultLength = ceil(log(inputNumber)/log(26)) (not vb6 code, but you get the idea) I haven't written (2) yet, but I assume it will be pretty easy.

Number 1, though, proved to be a bit of a doozy since A-Z can represent 0-25 in some places and 1-26 in others. If A is 0, then Z is 25, but unless you want to use BA for 26, skipping A_, then you have to let A be equal to 1 where it is a leading character (e.g. AA, AB, etc.) when the result is not 1 character long (i.e. for the result of A, A is 0 even though it is the leading character.)

It gets trickier, however. One result of the fact that A-Z represents 0-25 in certain slots and 1-26 in others is that figuring out how many characters the result will be is tricky.

Examples:

If input < 26^1, Then
 resultLength = 1
Elseif input < (27*(26^1) -1) Then 'ZZ represents (27*26) -1
 resultLength = 2
Elseif input < (27*(26^2) -1) Then 'ZZ represents (27*26^2) -1
 resultLength = 3
End If

Anyways, I'm pretty sure it works, so I'll let you take a look at it.

Function NumericToAlpha(inString As String) As String
 Dim outString As String
 Dim asLong As Long
 Dim Index As Integer
 Dim i As Integer
 Dim j As Double
 Dim intDiv As Integer
 asLong = Val(inString)
 ' validate input; no negative numbers or decimals
 If asLong < 0 Or InStr(inString, ".") Then
 MsgBox "Cannot process negative values or numbers that include a decimal point."
 NumericToAlpha = ""
 Exit Function
 End If
 Do While (27 * 26 ^ (Index) - 1) < asLong
 Index = Index + 1
 Loop
 If asLong = 26 Then Index = 1
 For i = Index To 0 Step -1
 j = 26 ^ i
 If (i = Index) And (Index > 1) And Int(asLong / j) = 1 Then asLong = asLong - 26 ^ (i - 1)
 intDiv = Int(asLong / j)
 If Len(outString) Then ' non-leading character
 outString = outString & Chr(Asc("A") + intDiv)
 Else 'outString leading character
 If i = 0 Then
 outString = Chr(Asc("A") + intDiv)
 Else
 outString = Chr(Asc("A") + intDiv - 1)
 End If
 End If
 asLong = asLong - (intDiv * j)
 Next i
 NumericToAlpha = outString
End Function

I test this with the following numbers, which I've come up with by doing the math by hand:

Dim newL As String
newL = Chr(13)
MsgBox NumericToAlpha(0) & newL & NumericToAlpha(25) & newL & NumericToAlpha(26) & newL & NumericToAlpha(701) & newL & NumericToAlpha(702) & newL & NumericToAlpha(18251) & newL & NumericToAlpha(18252) & newL & NumericToAlpha(474551) & newL & NumericToAlpha(474552) & newL & NumericToAlpha(474551) & newL & NumericToAlpha(474552)

If there are any bugs or if you can think up a sexier way to do this, please let me know!

• Ariel: Instead of doing it yourself, use this: gladman.plushost.co.uk/oldsite/computing/gmp4win.php and mpir.org also see: exploringbinary.com/… Then gmp_strval(number, 36) Or 62 (instead of 36) to use both upper and lowercase letters.
• Michael: @Ariel My program is in VB6, not C++. I know you can include C++ code in a VB6 program somehow (don't know if people are referring to .dll calls or what when they say you can do this), but I've never done it before. On a side not, I am rather surprised to see that there exist numerous web sites dedicated to a library made up of this function and other similar ones.
• Ariel: gmp is a very useful library. It's not just for this base conversion - it's for arbitrary precision math and it's one of the best.
• milahu: see also bideowego.com/base-26-conversion

Answer 1

First Point:

For multiple If/Else blocks, I prefer to use Select Case. I would just do this out of habit, though your check is small enough that it may not matter too much.

Also, this may be trickier, given that something falling into the first case looks like it would also fall into the other two. However, unlike other languages, there is no break required, and the Select block ends at the first case it comes to (like the If/Else would have).

Select Case input
Case Is < 26 ^ 1:
 resultLength = 1
Case Is < (27 * (26 ^ 1) - 1): 'ZZ represents (27*26) -1
 resultLength = 2
Case Is < (27 * (26 ^ 2) - 1): 'ZZ represents (27*26^2) -1
 resultLength = 3
End Select

---

Second Point:

I know I'm not doing much in the way of efficiency (more just style, I think), but here's another. Regarding:

If (i = Index) And (Index > 1) And Int(asLong / 26 ^ (i)) = 1 Then
 j = 26 ^ i
 asLong = asLong - 26 ^ (i - 1)
Else
 j = 26 ^ i
End If

Since you assign j = 26 ^ i in both cases, why not just assign it before the If?

j = 26 ^ i
If (i = Index) And (Index > 1) And Int(asLong / 26 ^ (i)) = 1 Then
 asLong = asLong - 26 ^ (i - 1)
End If

---

_{Sorry for not getting this all out at once. I'll continue to pick apart as I have time and add to this answer as needed...}

• Michael: The If/ElseIf portion was just code to explain the concept. In the actual code, I used a Do While loop. Although the upper limit of the Long data type probably wouldn't take too long to hit with a base of 26, if this were base 2, the switch/case statement would be very long. Seeing as how the do while loop can handle everything you could throw at it, and is the best solution for other bases, I guess I just see it as proper form.
• Michael: RE your edit: Yes, sorry; it is indeed redundant. I was rather tired when I wrote code. Whoops!
• Gaffi: @Michael No worries. I've written plenty of code that I knew better not to after the fact!

Answer 2

You asked for a 'sexier' way to do this, and I think I've got it, but I'll let you be the judge:

Function NumToAlph(inString As String) As String
Dim outString As String
Dim i As Integer
Dim inLong As Long
Dim MaxDigits As Byte
inLong = CLng(inString)
MaxDigits = 10
ReDim Vals(MaxDigits)
' Identify character code for last (right-most) result digit or special case/single-digit results
If inLong >= 27 Then
 inLong = inLong - 1
 Vals(0) = inLong Mod 26
 inLong = Int(inLong / 26)
Else
 Vals(0) = inLong Mod 27
 inLong = Int(inLong / 27)
End If
' Identify character code for all other result digits
For i = 1 To MaxDigits
 Vals(i) = inLong Mod 27
 inLong = Int(inLong / 27)
Next i
' Append character to output string, except for last result digit
For i = MaxDigits To 1 Step -1
 If Vals(i) > 0 Then
 outString = outString & Chr(Vals(i) + 64)
 ElseIf Len(outString) > 0 Then
 outString = outString & "A"
 End If
Next i
If Len(outString) > 0 Then ' Adjust multiple-digit result to account for 0 base when value > 26
 Vals(0) = Vals(0) + 1
End If
outString = outString & Chr(Vals(0) + 64) ' Append final character to output string
NumToAlph = outString
End Function

I've tested this up to 1,000,000,000, which comes out as BRJAHKL (longs don't get any more significant digits...). There might be some improvements that can be bade in the for loops (Why loop twice? Because it's what I could manage for now...), but I like this pretty well the way it is.

Answer 3

I realize this is an old post, but no one mentioned this.

' validate input; no negative numbers or decimals
If asLong < 0 Or InStr(inString, ".") Then
 MsgBox "Cannot process negative values or numbers that include a decimal point."
 NumericToAlpha = ""
 Exit Function
End If

VB6 doesn't allow for short circuiting, so you might as well break this into two separate If statements. Both conditions are always being checked anyway. This has an added benefit of giving a more specific error message to the end user as well.

To keep the extra code from cluttering up your main function, I recommend creating a private IsValidInput that returns a boolean.

Private Function IsValidInput(str As String) As Boolean
 ' validate input; no negative numbers or decimals
 If asLong < 0 Then
 MsgBox "Cannot process negative values."
 Exit Function
 End If
 If InStr(inString, ".") Then
 MsgBox "Cannot process numbers that include a decimal point."
 Exit Function
 End If
 ' If we've gotten here, input is valid
 IsValidInput = True
End Function

Personally, I would raise errors rather than show message boxes, but I've not gone through the trouble here, but you should consider it. Especially if your function resides in a class.

---

P.S. You don't use Hungarian notation anywhere except the intDiv variable. Drop the int part and give that variable a more meaningful name.