Convert Column Index into Corresponding Column Letter

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;
}

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)))

How to convert column number into a letter

Found this code somewhere:

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

Then for your case:

var columns = [];
for( i = 0; i< 10; i++)
columns.push(NUM_RETURN_LETRA(+i));

return columns

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 Index to Column A1 Notation and vice-versa

This was an interesting problem to tackle. The solution involves 2 functions:

indexToColumn(int) (string, error) will convert an index to A1 Notation. e.g. 703 to AAA

columnToIndex(string) (int, error) will convert A1 Notation to an index. e.g. BA to 53

Here is the code:

// indexToColumn takes in an index value & converts it to A1 Notation
// Index 1 is Column A
// E.g. 3 == C, 29 == AC, 731 == ABC
func indexToColumn(index int) (string, error) {

// Validate index size
maxIndex := 18278
if index > maxIndex {
return "", web.Errorf("index cannot be greater than %v (column ZZZ)", maxIndex)
}

// Get column from index
l := "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
if index > 26 {
letterA, _ := indexToColumn(int(math.Floor(float64(index-1)/26)))
letterB, _ := indexToColumn(index%26)
return letterA + letterB, nil
} else {
if index == 0 {
index = 26
}
return string(l[index-1]), nil
}

}

// columnToIndex takes in A1 Notation & converts it to an index value
// Column A is index 1
// E.g. C == 3, AC == 29, ABC == 731
func columnToIndex(column string) (int, error) {

// Calculate index from column string
var index int
var a uint8 = "A"[0]
var z uint8 = "Z"[0]
var alphabet = z - a + 1
i := 1
for n := len(column) - 1; n >= 0; n-- {
r := column[n]
if r < a || r > z {
return 0, web.Errorf("invalid character in column, expected A-Z but got [%c]", r)
}
runePos := int(r-a) + 1
index += runePos * int(math.Pow(float64(alphabet), float64(i-1)))
i++
}

// Return column index & success
return index, nil

}

How to get the column letter of a cell?

CellReference.convertNumToColString is a static method. So no new CellReference object needed. And it needs the numeric column index from the cellwhich can be get via Cell.getColumnIndex.

So

String column_letter = CellReference.convertNumToColString(cell.getColumnIndex());

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])

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 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)

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



Leave a reply



Submit