Reports

I view this more of a math problem than a coding problem.

I will just illustrate how I am thinking about the problem with a couple examples and then generalize.

Consider the number 438:

The 4 is in the 100's place.
The 3 is in the 10's place.
The 8 is in the 1's place.

In order to compute the number, the formula is:

(Remember that any number to the zeroth power is 1, and that the numbers cycle in powers of 10.)

Similarly, how do you compute BFX for example?

Note that there are 26 letters in the alphabet.

The formula is similar:

Now replace B with 2, F with 6, and X with 24:

You get:

1352 + 156 + 24 = 1532

With those 2 examples in mind, here is a function (columnNumber) that will convert 3 letters to a number in the convention of excel columns. It takes up to a 3 letter string and converts the value to a number.

Note, per makers of excel, the last column is 'XFD' as there are a maximum number of columns of 16,384. Other than making the input upper case and ensuring 3 characters, I have not done any type of validation of the input as to not distract from the essence of the function.

function columnNumber(str) {
  //note that strings are zero based so the 
  //indexed position of the letter in the string
  //will tell you the letters numerical value
  //So A's index is 1, Z's index is 26
  let alphabet = "_ABCDEFGHIJKLMNOPQRSTUVWXYZ";

  //first ensure the string is 3 characters long
  //I do this by adding to the beginning of the 
  //string then slicing off the last 3 characters.

  str = "___" + str;
  str = str.slice(-3);
  str = str.toUpperCase(); //just in case

  //get the left middle and right letter/character
  let leftChar = str.substring(0, 1);
  let middleChar = str.substring(1, 2);
  let rightChar = str.substring(2);

  //convert the 3 letters to corrresponding numbers
  let leftCharValue = alphabet.indexOf(leftChar);
  let middleCharValue = alphabet.indexOf(middleChar);
  let rightCharValue = alphabet.indexOf(rightChar);

  //compute the numericalValue
  let numericalValue = leftCharValue * 26 * 26 + middleCharValue * 26 + rightCharValue;
  return (numericalValue);
}

function calculate() {
  let str = document.getElementById("char-input").value;
  document.getElementById("result").innerHTML = columnNumber(str);
}

document.getElementById("calc-button").addEventListener("click", calculate);

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Column String Heading Calculator</title>
  <style>
    input,
    button {
      display: block;
    }
  </style>
</head>

<body>
  <input id="char-input" type="text" value="XFD">
  <button id="calc-button">Calculate</button>
  <div id="result"></div>
  <script src="columnCalculator.js"></script>
</body>

</html>

79103992

I view this more of a math problem than a coding problem.