Excel Sheet Column Title

Updated on 26 May, 2025
Excel Sheet Column Title header image

Problem Statement

In Excel, columns are identified using a sequence of one or more letters. The challenge presented involves converting a given column number into its corresponding alphabetical title as used in Excel. The sequence starts with A representing 1, B for 2, continuing on to Z for 26. Post Z, the titles continue with two letters starting from AA for 27, AB for 28, and so on. The task is to derive this column title from a given integer input.

Examples

Example 1

Input:

columnNumber = 1

Output:

"A"

Example 2

Input:

columnNumber = 28

Output:

"AB"

Example 3

Input:

columnNumber = 701

Output:

"ZY"

Constraints

  • 1 <= columnNumber <= 231 - 1

Approach and Intuition

The problem can be seen as converting a base-10 number (columnNumber) into a base-26 number using characters A through Z instead of standard numerical digits.

  1. Each letter in the English alphabet corresponds to a digit in this base-26 number system, with A acting as 1, B as 2, up to Z for 26.

  2. The process to convert involves repeatedly determining the remainder when the current number is divided by 26. This remainder corresponds to a character from the column title, starting from the end.

  3. Since zero is not representable by any letter (A starts from 1), if the remainder is zero, it indicates a 'Z' in the title, and we need to adjust the column number accordingly.

  4. After determining each letter, adjust the original number by subtracting the value corresponding to the identified letter and then divide by 26. This process continues until the number becomes zero.

  5. The previously calculated characters are noted in reverse. After the loop ends, reversing the accumulated characters results in the column title.

Example elucidation based on the constraints:

  • For columnNumber = 1, the output is "A". This is directly derived as 1 % 26 → 1, corresponds to 'A'.

  • For columnNumber = 28, repeatedly evaluating and adjusting 28 gives:

    • 28 % 26 → 2 (remainder), corresponding letter is 'B'
    • Adjusted number = (28 - 2) / 26 = 1, this leads to 'A'.
    • Result is "AB".
  • For columnNumber = 701, a similar process follows:

    • Iteration steps deliver 'Y' for the last position and 'Z' for second-last, resulting in "ZY".

Solutions

  • C++
  • Java
  • C
  • JavaScript
  • Python
cpp
class Solution {
public:
    string columnToTitle(int colNum) {
        string result;

        while (colNum) {
            colNum--;
            result = result + (char)(colNum % 26 + 'A');
            colNum /= 26;
        }

        reverse(result.begin(), result.end());
        return result;
    }
};

The provided C++ solution addresses the problem of converting a given integer (representing a column number in an Excel sheet) to its corresponding column title. To achieve this, the code implements the method columnToTitle within the Solution class, which processes the integer to generate a string representing the Excel column title.

Here's a breakdown of the steps involved in the solution:

  1. Initialize an empty string result which will store the resultant column title.
  2. Employ a loop that continues until colNum becomes zero. Within the loop, perform the following steps:
    • Decrement colNum by 1 to adjust the column number into a zero-indexed format suitable for modulus operation with 26.
    • Append the character corresponding to the current column batch (found by taking colNum % 26) to result. This operation translates the remainder, which is a number, into its respective character in the alphabet (where 'A' is 0th position).
    • Update colNum by dividing it by 26, reducing it to the next significant digit in the column number interpretation.
  3. After the loop, reverse the string result since the characters were appended in the reverse order of their appearance in the final title.
  4. Return the reversed string which is now the correctly formatted Excel column title.

This code effectively and efficiently maps an integer to its corresponding Excel-style column title using basic arithmetic and character operations, embodying an elegant solution to the problem using modulo and division to break down the column number.

java
class Solution {
    public String numberToTitle(int n) {
        StringBuilder result = new StringBuilder();
        
        while (n > 0) {
            n--;
            result.append((char) ((n % 26) + 'A'));
            n /= 26;
        }
        
        return result.reverse().toString();
    }
}

The given Java solution solves the problem of converting a given integer to its corresponding Excel sheet column title, much like how Excel labels its columns from A, B, C... to Z, AA, AB, etc. The function numberToTitle takes an integer n and generates the column title. The process involves:

  • Initializing a StringBuilder instance to build the resultant string.
  • Using a while loop that continues until n becomes zero:
    • The code first decrements n by 1. This adjustment is needed due to Excel columns starting from 1 (i.e., A = 1, Z = 26, AA = 27).
    • The character for the current column is determined by taking (n % 26), converting this remainder to its corresponding alphabet character using ASCII values, and appending it to the StringBuilder.
    • The value of n is then divided by 26 to shift to the next significant column position in the title.
  • After exiting the loop, the constructed string (which is in reverse order due to the calculation method) is reversed to reflect the correct column title order.

Finally, the method returns the correctly formatted column title as a string. The use of StringBuilder ensures efficient string manipulations, and the algorithm held within the loop efficiently handles the conversion of the numeric index to the spreadsheet column label format.

c
char* spreadsheetTitle(int colNum) {
    char* title = calloc(1020, sizeof(char));
    int cursor = 0;
    while (colNum > 0) {
        colNum--;
        title[cursor++] = 'A' + colNum % 26;
        colNum /= 26;
    }

    int left = 0, right = cursor - 1;
    while (left < right) {
        char tmp = title[left];
        title[left] = title[right];
        title[right] = tmp;
        left++;
        right--;
    }
    return title;
}

The provided C program is designed to convert a given column number into its corresponding Excel sheet column title, similar to how columns are labeled in spreadsheet applications. This function, named spreadsheetTitle, accepts an integer colNum and returns a string representing the column title.

To understand the logic:

  • Allocate memory for a string title to store the resulting column title.
  • Use a while loop to convert the column number into a sequence of characters representing the Excel title. For each iteration:
    • Decrement colNum to adjust for 0-based indexing inherent to character arrays.
    • Derive each character by taking the modulus of colNum by 26 and adding it to 'A'. This calculates the correct letter in the alphabet corresponding to the current division of the column number.
    • Divide colNum by 26 to move to the next letter in the sequence.
  • After computing the sequence in reverse order, reverse the string using another while loop:
    • Swap characters starting from the opposite ends of the sequence until the middle of the string is reached.
  • Return the correctly ordered column title.

This approach ensures that the column number is accurately transposed into the format used in Excel sheets, facilitating a straightforward integration with applications requiring column-related computations or mappings.

js
var excelColumnTitle = function (columnNum) {
    let result = "";

    while (columnNum > 0) {
        columnNum--;
        result =
            String.fromCharCode((columnNum % 26) + "A".charCodeAt(0)) + result;
        columnNum = Math.floor(columnNum / 26);
    }

    return result;
};

The provided JavaScript program converts a given column number into its corresponding Excel sheet column title. The function excelColumnTitle takes an integer columnNum as input, representing the column index, and returns its respective column title as a string.

Here's the breakdown of the program:

  • Initialize an empty string result to store the column title.
  • Use a while loop to execute the conversion until columnNum is greater than zero.
    • First, decrement columnNum by 1. This step adjusts the number to a zero-based index, allowing for the correct calculation of the column title.
    • Convert the current modulo of columnNum by 26 to a character. The modulo operation finds the remainder, which corresponds to a position in the alphabet. Use String.fromCharCode combined with the ASCII value of "A" to convert this numeric remainder to the respective letter.
    • Prepend this computed character to result, ensuring the construction of the column title from last to first letter.
    • Divide columnNum by 26 and update it using Math.floor to discard any decimals, preparing for the next iteration (if needed).
  • Upon completion of the loop, return the string result, which now contains the column title corresponding to the original column index number.

This approach effectively handles all numerical inputs by turning them into the format commonly seen in spreadsheet software, emulating the method for labeling columns with letters (A, B, ..., Z, AA, AB, ...).

python
class Solution:
    def columnTitle(self, n: int) -> str:
        result = ""
        while n > 0:
            n -= 1
            result += chr(n % 26 + ord("A"))
            n //= 26
        return result[::-1]

The provided Python code defines a method that converts a given integer n into a string that represents the corresponding column title in an Excel sheet. This column naming follows a sequence similar to alphabetical order, but instead using a base-26 numeral system where the letters A to Z represent the numbers 1 to 26.

To achieve this, the method performs the following operations:

  1. Initialize an empty string result which will gradually build the column title from the least significant digit to the most significant.
  2. Enter a while loop which will execute as long as n is greater than 0.
  3. Inside the loop, decrement n by 1 to adjust for the fact that the Excel columns start counting from 1 rather than 0.
  4. Convert the current value of n % 26 to its corresponding alphabet character using Python’s chr() function and append this character to result.
  5. Modify n to n // 26 to process the next higher place value in the next iteration.
  6. After the loop completes (when n becomes 0), the result string, which contains the characters in reverse order, is reversed using Python’s slicing technique result[::-1] to reflect the correct column title.

This method ensures that the input integer is cleanly converted to its respective Excel column label, accounting for all necessary adjustments in the calculation process.

Comments

No comments yet.