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();

```