I'm writing something that basically takes a bunch of files and renames them into a nice format, such as
pic_001.jpeg, etc. One of the options is to write the incrementation as letters, e.g.
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_117, then the
pic_aaa set would start at
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!