The alpha column headers in calc are created internally and automatically compensate for insertion or deletion of columns so there must be some mechanism by which LO itself correlates the column number to the alpha column ID. It appears to be impossible to generate these ALPHA IDs based upon current cursor location. Column() simply returns the numeric ID from 1 to 1024. Dragging an Alpha cell simply repeats the alpha character (or sequence). It would be possible to create a Vlookup table which would supply the correlation but that still requires the manual entry of all 1024 3 digit alpha IDs, whereas the numeric sequences could be either just “dragged” or a simple +1 formula autofilled for 1024 rows.
Is there a trick I haven’t discovered yet? If no trick, can we expect the functionality to be incorporated into a future release or is it a feature request?
These posted answers may be of help:
Just for completeness:
The “names” of columns in spreadsheets and in text tables as well are numbers - just written in an uncommon way:
In spreadsheets the letters “A” through “Z” are used as digits with the values 1 through 26 weighted by powers of 26 in basically the same way as the decimal notation does it with the digits “0” through “9” and powers of 10. Specifically uncommon is that there is no zero in this system, and that therefore strings representing numbers cannot be aligned by leading zeros. As example you may check
AB = 1*26 + 2 = 28 and
ABC = 1*(26^2)+2*26+3=731.
For text tables the representation of column numbers is even a bit funnier: Again “A” through “Z” have the values 1 through 26, but then come “a” through" “z” as additional digits with the values 27 through 52. The systems’s base is now 52 and by powers of 52 starting with
52^0=1 at the right end the digits ar weighted. We get
AbC= 1*(52^2)+28*(52^1)+3*52^0)=2704+1456+3=4163 now - and that’s no joke. Simply make a text table with 5000 columns and check. (By the way: decimal “5000”=“ArH” in text-table-column-header-fashion.)
Joking aside: I needed the conversion in a few cases and wrote the following functions in LibreOffice Basic therefore:
Function columnIndexForColumnName(pColName As String, pMode As Boolean) REM 1-based! REM The columns names MUST consist of plain Latin letters. Their values as digits REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" REM There is NO ZERO in this system. REM pMode=True (or<>0) tells that the column names are from TextTable, otherwise they are taken as if REM from a Spreadsheet where only the upper case letters ("A" through "Z") are used. If pMode Then radix = 52 digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" Else radix = 26 digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" REM To make sure an error is thrown if pColName contains an illegal character. REM Alternatively we might assign pColName = Ucase(pColName) for spreadsheet columns as INDIRECT() does. End If lenCN = Len(pColName) acc = 0 For j = 1 To lenCN d = Mid(pColName, j, 1) charValue = InStr(1, digits, d, 0) acc = acc*radix + charValue Next j columnIndexForColumnName = acc End Function Function columnNameForColumnIndex(pColNum As Long, pMode As Boolean) REM 1-based! REM The columns will consist of plain Latin letters. Their values as digits REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" REM There is NO ZERO in this system. REM pMode=True (or<>0) tells that the column names are made for TextTable. Otherwise for Spreadsheet. digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" If pMode Then radix = 52 Else radix = 26 End If nameStr = "" acc = pColNum Do While acc>0 dValue = (acc MOD radix) If dValue=0 Then dValue = radix acc = Int((acc - dValue)/radix) nameStr = Mid(digits, dValue, 1) & nameStr Loop columnNameForColumnIndex = nameStr End Function
Wow - what does the rest do?
Thanks for the tutorial and the inspiration it gave me to manipulate the 26 character A-Z alpha string with a simple repeating concatenating mid() function in calc. This was easily achieved for the first 700+ IDs with only 2 characters which I then simply copied into a [Sort List] and can now “cell drag” any alpha character to automatically generate the labels. I know I’m never going to need more than the 2 characters I generated so - Job Done.