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 cell
which 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
What's the Best Way to Make a D3.Js Visualisation Layout Responsive
Switch Statement for Multiple Cases in JavaScript
React-Router - Pass Props to Handler Component
JavaScript Asynchronous Return Value/Assignment with Jquery
How to "Await" for a Callback to Return
Input Type=File Show Only Button
How to Get the HTML for a Dom Element in JavaScript
Scroll with Anchor Without # in Url
Html5 - Cross Browser Iframe Postmessage - Child to Parent
Get Caret Index in Contenteditable Div Including Tags
Detect Rotation of Android Phone in the Browser with JavaScript
How to Make Exe Files from a Node.Js App
JavaScript Removeeventlistener Not Working
When Is the Comma Operator Useful
Image Loaded Event in for Ng-Src in Angularjs