thzinc

A1 Reference Style Spreadsheet Column Names - Interview question of the week from rendezvous with cassidoo

Nice little brain teaser involving number base conversion πŸ˜„

Interview question of the week

This week’s question: Spreadsheet programs often use the A1 Reference Style to refer to columns. Given a column name in this style, return its column number.

Examples of column names to their numbers:

A -> 1
B -> 2
C -> 3
// etc
Z -> 26
AA -> 27
AB -> 28
// etc
AAA -> 703

My solution

At it’s core, this is a conversion from base-26 with a custom set of digits to a base-10 number.

#mocha

  

function getColumn(input = "") {
  if (typeof input !== "string") return NaN;
  if (input.length === 0) return NaN;

  const normalized = input.toUpperCase();
  let acc = 0;
  for (let i = 0; i < normalized.length; i++) {
    // Get the ASCII value for the letter
    const c = normalized.charCodeAt(i);

    // If the ASCII value is not between A-Z inclusive, stop processing
    if (c < CAPITAL_A || CAPITAL_Z < c) return NaN;

    // Get the value of the character
    const value = c - CAPITAL_A;

    // Calculate the base (in base-26) to which to add the value, then add the result to the accumulated total
    acc += value + Math.pow(26, i);
  }

  return acc;
}

const CAPITAL_A = 0x41;
const CAPITAL_Z = 0x5a;


mocha.setup("bdd");
const assert = chai.assert;
const expect = chai.expect;
const should = chai.should();

describe("Given the examples from the question", () => {
  const expectations = [
    {
      input: "A",
      output: 1,
    },
    {
      input: "B",
      output: 2,
    },
    {
      input: "C",
      output: 3,
    },
    {
      input: "Z",
      output: 26,
    },
    {
      input: "AA",
      output: 27,
    },
    {
      input: "AB",
      output: 28,
    },
    {
      input: "AAA",
      output: 703,
    },
  ];
  expectations.forEach(({ input, output }) => {
    it(`should return ${output} for the input ${input}`, () => {
      getColumn(input).should.equal(output);
    });
  });
  expectations.forEach(({ input: originalInput, output }) => {
    const input = originalInput.toLowerCase();
    it(`should return ${output} for the input ${input}`, () => {
      getColumn(input).should.equal(output);
    });
  });
});

describe("Given invalid input", () => {
  it("should return NaN when the input is an empty string", () => {
    isNaN(getColumn("")).should.equal(true);
  });
  it("should return NaN when the input is not a string", () => {
    isNaN(getColumn(null)).should.equal(true);
    isNaN(getColumn(undefined)).should.equal(true);
    isNaN(getColumn([])).should.equal(true);
    isNaN(getColumn({})).should.equal(true);
    isNaN(getColumn(1)).should.equal(true);
  });
  it("should return NaN when the input contains a character that is not a letter A-Z", () => {
    isNaN(getColumn("1")).should.equal(true);
    isNaN(getColumn("*")).should.equal(true);
    isNaN(getColumn("A-Z")).should.equal(true);
    isNaN(getColumn("A1")).should.equal(true);
    isNaN(getColumn("0O")).should.equal(true);
  });
});

mocha.run();








      

      
      

        
See also