Convert numbers to letters beyond the 26 character alphabet

Question

I'm creating some client side functions for a mappable spreadsheet export feature.

I'm using jQuery to manage the sort order of the columns, but each column is ordered like an Excel spreadsheet i.e. a b c d e......x y z aa ab ac ad etc etc

How can I generate a number as a letter? Should I define a fixed array of values? Or is there a dynamic way to generate this?

Answer 1

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

Answer 2

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
}

Answer 3

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"

Answer 4

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"

Answer 5

You can use code like this, assuming that numbers contains the numbers of your columns. So after this code you'll get the string names for your columns:

var letters = ['a', 'b', 'c', ..., 'z'];
var numbers = [1, 2, 3, ...];
var columnNames = [];
for(var i=0;i<numbers.length;i++) {
    var firstLetter = parseInt(i/letters.length) == 0 ? '' : letters[parseInt(i/letters.length)];
    var secondLetter = letters[i%letters.length-1];
    columnNames.push(firstLetter + secondLetter);
}

Answer 6

This covers the range from 1 to 1000. Beyond that I haven't checked.

function colToletters(num) {
  let a = " ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  if (num < 27) return a[num % a.length];
  if (num > 26) {
    num--;
    let letters = ''
    while (num >= 0) {
      letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[num % 26] + letters
      num = Math.floor(num / 26) - 1
    }
    return letters;
  }
}

I could be wrong but I've checked the other functions in this answer and they seem to fail at 26 which should be Z. Remember there are 26 letters in the alphabet not 25.

Answer 7

Simple recursive solution:

function numberToColumn(n) {
  const res = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[n % 26];
  return n >= 26 ? numberToColumn(Math.floor(n / 26) - 1) + res : res;
}