Convert excel column alphabet (e.g. AA) to number (e.g., 25)

Question

In my grid the column headers are named A,B,C...,AA,AB,AC,...etc like an excel spreadsheet. How can I convert the string to number like: A => 1, B => 2, AA => 27

Answer 1

Try:

var foo = function(val) {
  var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;

  for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
    result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
  }

  return result;
};

console.log(['A', 'AA', 'AB', 'ZZ'].map(foo)); // [1, 27, 28, 702]

Answer 2

solution 1: best performance and browser compatibility

// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
    var chrs = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ', mode = chrs.length - 1, number = 0;
    for(var p = 0; p < letters.length; p++){
        number = number * mode + chrs.indexOf(letters[p]);
    }
    return number;
}

solution 2: best performance and compatibility and shorter code (Recommended)

// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
    for(var p = 0, n = 0; p < letters.length; p++){
        n = letters[p].charCodeAt() - 64 + n * 26;
    }
    return n;
}

solution 3: short code (es6 arrow function)

// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
    return letters.split('').reduce((r, a) => r * 26 + parseInt(a, 36) - 9, 0);
}

test:

['A', 'Z', 'AA', 'AB', 'ZZ','BKTXHSOGHKKE'].map(lettersToNumber);
// [1, 26, 27, 28, 702, 9007199254740991]

lettersToNumber('AAA'); //703

Answer 3

Here's a quick example of the code you should implement. This will work with any given number of letters.

function letterToNumbers(string) {
    string = string.toUpperCase();
    var letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', sum = 0, i;
    for (i = 0; i < string.length; i++) {
        sum += Math.pow(letters.length, i) * (letters.indexOf(string.substr(((i + 1) * -1), 1)) + 1);
    }
    return sum;
}

Answer 4

// Given Column to Number
function colToNumber(str) {
  let num = 0
  let i = 0
  while (i < str.length) {
    num = str[i].charCodeAt(0) - 64 + num * 26;
    i++;
  }
  return num;
}

//Given Number to Column name
function numberToCol(num) {
  let str = '', q, r;
  while (num > 0) {
    q = (num-1) / 26;
    r = (num-1) % 26
    num = Math.floor(q)
    str = String.fromCharCode(65 + r) + str;
  }
  return str;
}

Answer 5

i just wrote a junk yard f@#$ snippet... need to be optimized.. :)

charToNum = function(alpha) {
        var index = 0
        for(var i = 0, j = 1; i < j; i++, j++)  {
            if(alpha == numToChar(i))   {
                index = i;
                j = i;
            }
        }
        console.log(index);
    }

numToChar = function(number)    {
        var numeric = (number - 1) % 26;
        var letter = chr(65 + numeric);
        var number2 = parseInt((number - 1) / 26);
        if (number2 > 0) {
            return numToChar(number2) + letter;
        } else {
            return letter;
        }
    }
chr = function (codePt) {
        if (codePt > 0xFFFF) { 
            codePt -= 0x10000;
            return String.fromCharCode(0xD800 + (codePt >> 10), 0xDC00 + (codePt & 0x3FF));
        }
        return String.fromCharCode(codePt);
    }

charToNum('A') => returns 1 and charToNum('AA') => returns 27;

Answer 6

I rewrote Yoshi's answer in a more verbose form that explains better how it works and is easier to port to other languages:

var foo = function(val) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    var baseNumber = base.length;

    var runningTotal = 0;
    var characterIndex = 0;
    var indexExponent = val.length - 1;

    while( characterIndex < val.length ){
        var digit = val[characterIndex];
        var digitValue = base.indexOf(digit) + 1;
        runningTotal += Math.pow(baseNumber, indexExponent) * digitValue;

        characterIndex += 1
        indexExponent -= 1
    }

    return runningTotal;
};

console.log(['A', 'AA', 'AB', 'ZZ'].map(foo)); // [1, 27, 28, 702]

Answer 7

Public Function ColLet2Num(Letras As String)
'RALONSO MAYO 2017
'A-> 1
'OQ ->407
'XFD->16384
Dim UnChar As String
Dim NAsc As Long
Dim F As Long
Dim Acum As Long
Dim Indice As Long
Letras = UCase(Letras)
Acum = 0
Indice = 0
For F = Len(Letras) - 1 To 0 Step -1

    UnChar = Mid(Letras, F + 1, 1)
    NAsc = Asc(UnChar) - 64
    Acum = Acum + (NAsc * (26 ^ Indice))
    Indice = Indice + 1
Next
If Acum > 16384 Then
    MsgBox "La celda máxima es la XFD->16384", vbCritical
End If
ColLet2Num = Acum
End Function

Answer 8

    const getColumnName = (columnNumber) => {
        let columnName = "";
        const alphabets = "abcdefghijklmnopqrstuvwxyz".toUpperCase();
        while (columnNumber > 0) {
            const rem = columnNumber % 26;
            if (rem === 0) {
                columnName += "Z";
                columnNumber = columnNumber / 26 - 1;
            } else {
                columnName += alphabets[rem - 1];
                columnNumber = Math.floor(columnNumber / 26);
            }
        }
        return columnName.split("").reverse().join("");
    };

    console.log(getColumnName(27));

Answer 9

A good readability and performance example:

const letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
// create dict to O(1) access to letter number
const dict = Object.fromEntries(letters.split('').map((it, index) => [it, index + 1]));

function toNumber(col) {
    return col
        .toUpperCase()
        .split('')
        .reduce((acc, letter, index) => acc + Math.pow(letters.length, col.length - (index + 1)) * dict[letter], 0);
}

Answer 10

Highly inspired by the different solutions put forth on this page

//fixed the one taken from here
function colToNumber(str: string): number {
    var num = 0

    for (var i = 0; i < str.length; i++) {
        const current_letter = str.charCodeAt(i) - 64
        const current_char = str[i]
        if (i == str.length - 1) {
            num += current_letter

        } else {
            var current = current_letter * Math.pow(26, str.length - i - 1)
            num += current
        }

    }
    return num;
}

//Given Number to Column name (taken from here)
function numberToCol(num: number) {
    var str = '', q: number, r: number;
    while (num > 0) {
        q = (num - 1) / 26;
        r = (num - 1) % 26
        num = Math.floor(q)
        str = String.fromCharCode(65 + r) + str;
    }
    return str;
}


function test_both() {
    const dic = new Map<number, string>()
    dic.set(1,"A")
    dic.set(10,"J")
    dic.set(13,"M")
    dic.set(33,"AG")
    dic.set(63,"BK")
    dic.set(66,"BN")
    dic.set(206,"GX")
    dic.set(502,"SH")
    dic.set(1003,"ALO")
    dic.set(100,"CV")
    
    dic.set(10111,"NXW")
    dic.set(10001,"NTQ")
    dic.set(9002,"MHF")
    dic.set(5002,"GJJ")
    dic.set(3002,"DKL")
    dic.set(16384,"XFD")

    for (var key of dic.keys()) {
        const expected_a1 = dic.get(key) || ""
        //console.log(`${ key }, ${ expected_a1 } `)
        var actual = numberToCol(key)

        var actual_num = colToNumber(expected_a1)

        if (actual.localeCompare(expected_a1) != 0) {

            console.error(`key = ${key} == expected=${expected_a1} actual = ${actual} `)

        }
        if (actual_num != key) {
            console.error(`expected = ${expected_a1} key = ${key} == actual = ${actual_num} `)

        }


    }
}