Converting Excel Column Numbers to Alphabetic Titles: A JavaScript Approach in simple 9 Steps

Leet Code: 168. Excel Sheet Column Title

If you’ve ever worked with Excel, you might be familiar with column numbers (e.g., A, B, C, …, Z, AA, AB, AC, …, ZZ, AAA, AAB, etc.). These alphanumeric titles are commonly used to refer to specific columns in a spreadsheet. However, have you ever wondered how you can programmatically convert a column number to its corresponding title? In this article, we’ll explore a JavaScript function that does just that.

Image from Pixels “Man Coding”

The Code

Let’s start by taking a closer look at the code:

var convertToTitle = function(columnNumber) {
    const numberToCharacterMap = {};
    for (let i = 1; i <= 26; i++) {
        const character = String.fromCharCode(i + 64);
        numberToCharacterMap[i] = character;
    }
    let resp = "";
    while(columnNumber > 0) {
        let remainder = columnNumber % 26;
        if (remainder === 0) {
            resp = 'Z' + resp;
            columnNumber = Math.floor(columnNumber / 26) - 1;
        } else {
            resp = numberToCharacterMap[remainder] + resp;
            columnNumber = Math.floor(columnNumber / 26);
        }
    }
    return resp;
};

Understanding the Code

Let’s break down this code step by step:

1. const numberToCharacterMap = {};

This line initializes an empty object called numberToCharacterMap. This object will be used to map numbers to their corresponding alphabetic characters (1 maps to 'A', 2 maps to 'B', and so on).

2. for (let i = 1; i <= 26; i++) { ... }

This loop populates the numberToCharacterMap object. It iterates from 1 to 26 (inclusive), which corresponds to the 26 letters of the alphabet.

3. const character = String.fromCharCode(i + 64);

This line converts the current value of i into its corresponding character using the String.fromCharCode() method. Since 'A' has a character code of 65, we add 64 to i to get the correct character code.

4. numberToCharacterMap[i] = character;

This line assigns the character (e.g., ‘A’, ‘B’, ‘C’, …) to the corresponding number in the numberToCharacterMap object.

5. let resp = "";

This initializes an empty string called resp, which will be used to store the resulting title.

6. while(columnNumber > 0) { ... }

This initiates a while loop. As long as columnNumber is greater than 0, the loop will continue.

7. let remainder = columnNumber % 26;

Inside the loop, this line calculates the remainder when columnNumber is divided by 26. This is used to determine the current character.

8. if (remainder === 0) { ... } else { ... }

Depending on whether the remainder is zero or not, the code executes one of two branches.

• If the remainder is zero, it means the column number is a multiple of 26. In this case, we append ‘Z’ to the resp string and adjust columnNumber accordingly.
• If the remainder is non-zero, we use the numberToCharacterMap to find the corresponding character and append it to resp.

9. return resp;

Finally, the resulting title is returned.

Conclusion

This JavaScript function, convertToTitle, provides an elegant solution to convert Excel column numbers into their corresponding alphabetic titles. By leveraging a mapping object and some arithmetic operations, it efficiently handles various cases. This function can be a valuable tool for tasks involving spreadsheet manipulation in your JavaScript applications. Happy coding!