supersonic 4 Posted June 10, 2010 (edited) 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 Share this post Link to post Share on other sites
Spiff59 54 Posted June 11, 2010 (edited) 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 Share this post Link to post Share on other sites
supersonic 4 Posted June 12, 2010 Cool! And very fast! Share this post Link to post Share on other sites
