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.
AutoIt
; ---------------------------------------------------------------------------------------------------- ; Function Name: _ExcelColumnLetter() ; Description: Converts Microsoft Excel column number (1 - 16384) into column letter(s) ; [<a href='http://www.freevbcode.com/ShowCode.asp?ID=9264].' class='bbc_url' title='External link' rel='nofollow external'>http://www.freevbcode.com/ShowCode.asp?ID=9264].</a> ; 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 by supersonic, 10 June 2010 - 06:50 PM.
