Convert excel column alphabet (e.g. AA) to number (e.g., 25)
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;
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;
}
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]
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