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, here is the code, and I'm pretty sure it works, so I'll let you guys 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!

Answer 1

Instead of doing it yourself, use this: http://gladman.plushost.co.uk/oldsite/computing/gmp4win.php and http://www.mpir.org/ also see: http://www.exploringbinary.com/how-to-install-and-run-gmp-on-windows-using-mpir/

Then gmp_strval(number, 36)

Or 62 (instead of 36) to use both upper and lowercase letters.

Answer 2

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

Answer 3

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.