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

Simple recursive solution:

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

Answer 5

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 6

Here is an alternative approach that relies on .toString(26). It uses conversion to base-26 and then translates the characters so they are in the a..z range:

const conv = ((base, alpha) => { // Closure for preparing the function
    const map = Object.fromEntries(Array.from(alpha, (c, i) => [c, alpha[i + 10]]));
    return n => (n + base).toString(26).replace(/o*p/, "").replace(/./g, m => map[m]);
})(parseInt("ooooooooop0", 26), "0123456789abcdefghijklmnopqrstuvwxyz");

// Example:
for (let n = 0; n < 29; n++) console.log(n, conv(n));
console.log("...");
for (let n = 690; n < 705; n++) console.log(n, conv(n));

About the magical number

The magical value "ooooooooop0" is derived as follows:

• It is a number expressed in radix 26, in the standard way, i.e. where the ten digits also play a role, and then the first letters of the alphabet.
• The greatest "digit" in this radix 26 is "p" (the 16^th letter of the Latin alphabet), and "o" is the second greatest.
• The magical value is formed by a long enough series of the one-but-greatest digit, followed by the greatest digit and ended by a 0.
• As JavaScript integer numbers max out around Number.MAX_SAFE_INTEGER (greater integers numbers would suffer from rounding errors), there is no need to have a longer series of "o" than was selected. We can see that Number.MAX_SAFE_INTEGER.toString(26) has 12 digits, so precision is ensured up to 11 digits in radix 26, meaning we need 9 "o".
• This magical number ensures that if we add units to it (in radix 26), we will always have a representation which starts with a series of "o" and then a "p". That is because at some point the last digit will wrap around to 0 again, and the "p" will also wrap around to 0, bringing the preceding "o" to "p". And so we have this invariant that the number always starts with zero or more "o" and then a "p".

More generic

The above magic number could be derived via code, and we could make it more generic by providing the target alphabet. The length of that target alphabet then also directly determines the radix (i.e. the number of characters in that string).

Here is the same output generated as above, but with a more generic function:

function createConverter(targetDigits) {
    const radix = targetDigits.length,
          alpha = "0123456789abcdefghijklmnopqrstuvwxyz",
          map = Object.fromEntries(Array.from(alpha, 
                (src, i) => [src, targetDigits[i]]
          )), 
          base = parseInt((alpha[radix-1]+'0').padStart(
               Number.MAX_SAFE_INTEGER.toString(radix).length - 1, alpha[radix-2]
          ), radix),
          trimmer = RegExp("^" + alpha[radix-2] + "*" + alpha[radix-1]);
    return n => (n + base).toString(radix)
                          .replace(trimmer, "")
                          .replace(/./g, m => map[m]);
}

// Example:
const conv = createConverter("abcdefghijklmnopqrstuvwxyz");
for (let n = 0; n < 29; n++) console.log(n, conv(n));
console.log("...");
for (let n = 690; n < 705; n++) console.log(n, conv(n));

This can now easily be adapted to use a more reduced target alphabet (like without the letters "l" and "o"), giving a radix of 24 instead of 26:

function createConverter(targetDigits) {
    const radix = targetDigits.length,
          alpha = "0123456789abcdefghijklmnopqrstuvwxyz",
          map = Object.fromEntries(Array.from(alpha, 
                (src, i) => [src, targetDigits[i]]
          )), 
          base = parseInt((alpha[radix-1]+'0').padStart(
               Number.MAX_SAFE_INTEGER.toString(radix).length - 1, alpha[radix-2]
          ), radix),
          trimmer = RegExp("^" + alpha[radix-2] + "*" + alpha[radix-1]);
    return n => (n + base).toString(radix)
                          .replace(trimmer, "")
                          .replace(/./g, m => map[m]);
}

// Example without "l" and "o" in target alphabet:
const conv = createConverter("abcdefghijkmnpqrstuvwxyz");
for (let n = 0; n < 29; n++) console.log(n, conv(n));
console.log("...");
for (let n = 690; n < 705; n++) console.log(n, conv(n));

Answer 7

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 8

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 9

An ES6 2Liner inspried by https://stackoverflow.com/a/71713806/22468906

const a2z = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
const day2Col = n => (n > 26 ? day2Col((n-1)/26|0) : '') + a2z[(n-1)%26]

Need to note that many of above answers, even the accepted answer, are wrong that AAA should come from an odd number (thus wrong for 702 -> AAA)

Referring to this exhausted list, 702 should give ZZ, 703 then AAA