_ExcelColumnLetter UDF

[supersonic]

Hi!

I was looking for an UDF to convert MS Excel column numbers above 256 (to 16384) into column letters.

I found an older UDF written by Jos here in the forum but this one was limited to max. 256 columns.

So, I googled a bit and finally found a VB script (http://www.freevbcode.com/ShowCode.asp?ID=9264)

which I converted into AU3. May be it's useful for others too.

; ----------------------------------------------------------------------------------------------------
; Function Name:    _ExcelColumnLetter()
; Description:      Converts Microsoft Excel column number (1 - 16384) into column letter(s)
;                   [http://www.freevbcode.com/ShowCode.asp?ID=9264].
; Syntax:           _ExcelColumnLetter([$iColumn = 0])
; Parameter(s):     $iColumn - The column number to convert into column letter(s).
; Requirement(s):   None.
; Return Value(s):  Success - "A" to "XFD", @error = 0, @extended = 1, 2, 3.
;                   Failure - "", @error = 1, @extended = 0.
; ----------------------------------------------------------------------------------------------------
Func _ExcelColumnLetter($iColumn = 0)
    Switch $iColumn
        Case 1 To 26 ; Single letter columns (1 = "A" - 26 = "Z").
            Local $letter1 = Chr($iColumn + 64)
            Return SetError(0, 1, $letter1)
        Case 27 To 702 ; Double letter columns (27 = "AA" - 702 = "ZZ").
            Local $letter1 = Chr(Int(($iColumn - 1) / 26) + 64)
            Local $letter2 = Chr(Mod(($iColumn - 1), 26) + 65)
            Return SetError(0, 2, $letter1 & $letter2)
        Case 703 To 16384 ; Triple letter columns (703 = "AAA" - 16384 = "XFD" [18278 = "ZZZ"]).
            Local $letter1 = Chr(Mod(Int(($iColumn - 1 - 26 - 676) / 676), 676) + 65)
            Local $letter2 = Chr(Mod(Int(($iColumn - 1 - 26) / 26), 26) + 65)
            Local $letter3 = Chr(Mod(($iColumn - 1), 26) + 65)
            Return SetError(0, 3, $letter1 & $letter2 & $letter3)
    EndSwitch
    Return SetError(1, 0, "")
EndFunc

Please report bugs.

Greets,

-supersonic.

Edited June 10, 2010 by supersonic

[Spiff59]

Am bored and thought I'd mess around with the code. I love 'formulaic' challenges lol. This seems to work also:

Func _ExcelColumnLetter($iColumn = 0)
    Local $letters
    While $iColumn
        $x = Mod($iColumn, 26)
        If $x = 0 Then $x = 26
        $letters = Chr($x + 64) & $letters
        $iColumn = ($iColumn - $x) / 26
    WEnd
    Return $letters
EndFunc

It converts any size number.

Edited June 11, 2010 by Spiff59

[supersonic]

Cool! And very fast!

[exodius]

Well done.