Convert numbers to letters beyond the 26 character alphabet
I think you're looking for something like this
function colName(n) {
var ordA = 'a'.charCodeAt(0);
var ordZ = 'z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while(n >= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}
// Example:
for(n = 0; n < 125; n++)
document.write(n + ":" + colName(n) + "<br>");
This is a very easy way:
function numberToLetters(num) {
let letters = ''
while (num >= 0) {
letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[num % 26] + letters
num = Math.floor(num / 26) - 1
}
return letters
}
function getColumnDescription(i) {
const m = i % 26;
const c = String.fromCharCode(65 + m);
const r = i - m;
return r > 0
? `${getColumnDescription((r - 1) / 26)}${c}`
: `Column ${c}`
}
Usage:
getColumnDescription(15)
"Column P"
getColumnDescription(26)
"Column AA"
getColumnDescription(4460)
"Column FOO"
If you have your data in a two-dimensional array, e.g.
var data = [
['Day', 'score],
['Monday', 99],
];
you can map the rows/columns to spreadsheet cell numbers as follows (building on the code examples above):
function getSpreadSheetCellNumber(row, column) {
let result = '';
// Get spreadsheet column letter
let n = column;
while (n >= 0) {
result = String.fromCharCode(n % 26 + 65) + result;
n = Math.floor(n / 26) - 1;
}
// Get spreadsheet row number
result += `${row + 1}`;
return result;
};
E.g. the 'Day' value from data[0][0] would go in spreadsheet cell A1.
> getSpreadSheetCellNumber(0, 0)
> "A1"
This also works when you have 26+ columns:
> getSpreadSheetCellNumber(0, 26)
> "AA1"