# How to EASILY create an alpha sort list reflecting column IDs

Hi there,
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?

Hello,

These posted answers may be of help:

How can I get the current cell Column letter ?

How to convert a column index to a column name?

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
digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Else
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)
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
Else
End If
nameStr = ""
acc     = pColNum
Do While acc>0