Convert Spreadsheet Number to Column Letter

Convert spreadsheet number to column letter

start_index = 1   #  it can start either at 0 or at 1
letter = ''
while column_int > 25 + start_index:   
    letter += chr(65 + int((column_int-start_index)/26) - 1)
    column_int = column_int - (int((column_int-start_index)/26))*26
letter += chr(65 - start_index + (int(column_int)))

Convert number to alphabet that corresponds to Excel column alphabet

Thanks to the question provided by Rawing, here is my favored solution.

import xlsxwriter

column_number = 26
x = xlsxwriter.utility.xl_col_to_name(column_number-1)
print(x)

Convert column index into corresponding column letter

I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26):

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

Function to convert column number to letter?

This function returns the column letter for a given column number.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

testing code for column 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

Convert an excel or spreadsheet column letter to its number in Pythonic fashion

There is a way to make it more pythonic (works with three or more letters and uses less magic numbers):

def col2num(col):
    num = 0
    for c in col:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return num

And as a one-liner using reduce (does not check input and is less readable so I don't recommend it):

col2num = lambda col: reduce(lambda x, y: x*26 + y, [ord(c.upper()) - ord('A') + 1 for c in col])

Get Excel column letter based on column header - Python

You can try:

import xlsxwriter
col_no = df.columns.get_loc("col_name")
print(xlsxwriter.utility.xl_col_to_name(col_no))

What is the algorithm to convert an Excel Column Letter into its Number?

public static int ExcelColumnNameToNumber(string columnName)
{
    if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

    columnName = columnName.ToUpperInvariant();

    int sum = 0;

    for (int i = 0; i < columnName.Length; i++)
    {
        sum *= 26;
        sum += (columnName[i] - 'A' + 1);
    }

    return sum;
}

Convert column number to column letter in VBA

Split the $ out of an absolute cell address.

Sub AdressColumn()
    Sheet1.Range("B1").Value = split(Sheet1.Range("C1").address, "$")(1)
End Sub

... or split the colon out of the relative full column address.

Sub AdressColumn()
    Sheet1.Range("B2").Value = Split(Sheet1.Range("C1").EntireColumn.Address(0, 0), ":")(0)
End Sub

Convert Column Number to Related Column Letters in Google Spreadsheet with Ballerina

Use the convColNumToChars function as follows.

function convColNumToChars(int columnNumber) returns string {
    if (columnNumber != 0) {
        return convColNumToChars((columnNumber-1)/26) + genAsciiChar(columnNumber%26);
    } else {
        return "";
    }
}

function genAsciiChar(int charCode) returns string {
    string[] charSet = ["Z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
        "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y"];
    return charSet[charCode];
}

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

---

Related Topics