Convert a number to Excel column letter

Question

This is a JavaScript "port" of the following questions, which ask the same thing about C# and Python, respectively:

How do you convert a numerical number to an Excel column name ~~in C#~~ without using automation getting the value directly from Excel.

Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.

Would the linked duplicate work as an answer for you? – JvdV Jan 10 '23 at 10:26 — JvdV, Jan 10 '23 at 10:26

score 0 · Answer 1 · answered Jan 10 '23 at 10:21

You can use toExcelCol and fromExcelCol from the following code:

/** @param {number} n */
const divmodExcel = (n) => {
    const a = Math.floor(n / 26)
    const b = n % 26

    return b === 0 ? [a - 1, b + 26] : [a, b]
}

const uppercaseAlphas = Array.from({ length: 26 }, (_, i) =>
    String.fromCodePoint(i + 'A'.codePointAt(0)),
)

/** @param {number} n */
const toExcelCol = (n) => {
    const chars = []

    let d
    while (n > 0) {
        ;[n, d] = divmodExcel(n)
        chars.unshift(uppercaseAlphas[d - 1])
    }
    return chars.join('')
}

/** @param {string} str */
const fromExcelCol = (str) => [...str]
    .map((ch) => uppercaseAlphas.indexOf(ch))
    .reduce((n, i) => n * 26 + i + 1, 0)

console.log(`toExcelCol(702) => ${toExcelCol(702)}`) // ZZ
console.log(`toExcelCol(703) => ${toExcelCol(703)}`) // AAA
console.log(`fromExcelCol('ZZ') => ${fromExcelCol('ZZ')}`) // 702
console.log(`fromExcelCol('AAA') => ${fromExcelCol('AAA')}`) // 703

These are a port of @poke's answer on the Python version.

Note that these operate in the 1-indexed world of Excel, such that fromExcelCol('A') gives 1, not 0.

Convert a number to Excel column letter

1 Answers1