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

excel sheet column number python
list of excel column letters
excel column number
how to find column number in excel
excel vba convert column number to letter
excel convert column number to text
excel reference column by number
convert column number to column name in excel

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


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]

Excel Column Letter to Number Quick Reference, MS Excel columns has a pattern like A, B, C, …, Z, AA, AB, AC, …., AZ, BA, BB, … Given a column number, find its corresponding Excel column name. Example: n = 700. Remainder (n%26) is 24. So we put 'X' in output string and n So Convert 10026 to number system which does not have '0', we get (25 26)26 Convert column number to column letter: Please type this formula: =SUBSTITUTE(ADDRESS(1,200,4),1,"") into a blank cell to get the result, and then press Enter to get the column letter as follows: Note: In the above formula, the red number 200 is the column number that you want to convert to column letter, you can change it to your need.


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;
}

Find Excel column name from a given column number , For example, below image shows numbers corresponding to excel columns. the problem lies in the handling the boundary cases like number 26 corresponds to 'Z' and 27 corresponds to 'AA'. find index of next letter and concatenate the letter. // to the solution. // Here index 0 corresponds to 'A' and 25 corresponds to 'Z​'. To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. In the example shown, the formula in C5 is: = SUBSTITUTE(ADDRESS(1, B5,4),"1","")


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

Convert number into corresponding excel column name, For example, the column number 30 is converted into the equivalent of b into the corresponding alphabetical character (0 => A, 25 => Z) and  Column_number: the column number of which you want to get column letter. Example: Covert excel number to column letter Here we have some column numbers in B2:B5. We want to get corresponding column letter (A, B, C, etc) from that given number (1, 2, 3, etc.). Apply above generic formula here to get column letter from column number.


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;

How to convert Excel column numbers into alphabetical characters , To convert a column number to an Excel column letter (e.g. A, B, C, etc.) Letter A corresponds to the first column in an Excel worksheet, B to the second An Excelchat Expert solved this problem in 25 mins! excel. Solved by A. A. in 43 mins  Stack Overflow Public questions me how to code a proper function of converting numbers to excel letter columns using vb.net. to convert a column number (e.g


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]

Excel formula: Convert column number to letter - Excelchat, To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. Turning numbers into letters based on alphabetical order. this function converts a number to an excel-style column index for arbitrary n:


Excel formula: Convert column number to letter, Get Excel VBA code to convert Column Letter to number. June 25, 2020 i.e., the Column number for Column AA. Assume same example as above. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Convert excel column alphabet (e.g. AA) to number (e


Excel VBA - Convert Column Letter to Number, For example, if you enter =ROW() in cell D8, the value returned is 8. and stick with pure number-based formulas (e.g. using the column number instead of letter​  I'm looking for the opposite to this Q&A: Convert an excel or spreadsheet column letter to its number in Pythonic fashion. or this one but in python How to convert a column number (eg. 127) into an excel column (eg. AA)


How to get the current column name in Excel?, Col_letter: it is the reference of the column letter of which you want to get column number. Var2: Let’s see an example to make things clear. Example: Create an excel column letter to number converter