w3resource

JavaScript: Column number that relates to a column title

JavaScript Math: Exercise-98 with Solution

Write a JavaScript program to get the column number (integer value) related to a column title as it appears in an Excel sheet.

For example:
A -> 1
B -> 2
C -> 3
...
Z -> 26
AA -> 27
AB -> 28
...

Visualisation:

JavaScript Math: Column number that relates to a column title.

Test Data:
("C") -> 3
("AD") -> 30
("ZX") -> 700

Sample Solution-1:

HTML Code:

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>JavaScript program to Column number that relates to a column title</title>
</head>
<body>

</body>
</html>

JavaScript Code:

function test(text) {
    text = text.toUpperCase()
    var cl_no = 0
    var len = text.length;
    for (var i = 0; i < len; i++) {
        cl_no += (Math.pow(26, (len - i - 1)) * (text.charCodeAt(i) - 64));
    }
    return cl_no;
}
text = "C"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text));
text = "AD"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text));
text = "ZX"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text)); 

Sample Output:

Original text: C
Excel column title related with the said column number: 3
Original text: AD
Excel column title related with the said column number: 30
Original text: ZX
Excel column title related with the said column number: 700

Flowchart:

JavaScript: Column number that relates to a column title.

Live Demo:

See the Pen javascript-math-exercise-98 by w3resource (@w3resource) on CodePen.


Sample Solution-2:

JavaScript Code:

function test(text) {
    col_num = 0;
    for (el of text) {
        col_num = col_num * 26 + el.toLowerCase().charCodeAt(0) - "a".charCodeAt(0) + 1;
    }
    return col_num;
}
text = "C"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text));
text = "AD"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text));
text = "ZX"
console.log("Original text: " +text)
console.log("Excel column title related with the said column number: "+test(text));

Sample Output:

Original text: C
Excel column title related with the said column number: 3
Original text: AD
Excel column title related with the said column number: 30
Original text: ZX
Excel column title related with the said column number: 700

Flowchart:

JavaScript: Column number that relates to a column title.

Live Demo:

See the Pen javascript-math-exercise-98-1 by w3resource (@w3resource) on CodePen.


Improve this sample solution and post your code through Disqus

Previous: Excel column title related with a column number.
Next: Add digits until there is only one digit.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.