Excel Sheet Column Number and Title Conversion in C++

Excel Column to Number

Given a column title as appear in an Excel sheet, return its corresponding column number.

For example:
    A - 1
    B - 2
    C - 3
    ...
    Z - 26
    AA - 27
    AB - 28 

1
2
3
4
5
6
7
8
9
10

class Solution {
public:
    int titleToNumber(string s) {
        int r = 0;
        for (int i = 0; i < s.length(); i ++) {
            r = r * 26 + s[i] - 64;
        }
        return r;
    }
};

class Solution {
public:
    int titleToNumber(string s) {
        int r = 0;
        for (int i = 0; i < s.length(); i ++) {
            r = r * 26 + s[i] - 64;
        }
        return r;
    }
};

The O(n) solution checks for each character of the string. For example, ABC = A * 26 * 26 + B * 26 + C. The left-most digit will be multiplied by 26 each iteration when a new digit is met. The Uppercase ‘A’ has ASCII code of 65 but since the ‘A’ equals to 1 not 0, we have to add one manually to the result, that is -65 + 1 = -64.

The Python Function: Python Function to Convert Excel Sheet Column Titles to Numbers

Number to Excel Column

Given a positive integer, return its corresponding column title as appear in an Excel sheet.

For example:
    1 - A
    2 - B
    3 - C
    ...
    26 - Z
    27 - AA
    28 - AB 

1
2
3
4
5
6
7
8
9
10
11

class Solution {
public:
    string convertToTitle(int n) {
        string r = "";
        while (n > 0) {
            r = (char)(65 + (n - 1) % 26) + r;
            n = (n - 1) / 26;
        }
        return r;
    }
};

class Solution {
public:
    string convertToTitle(int n) {
        string r = "";
        while (n > 0) {
            r = (char)(65 + (n - 1) % 26) + r;
            n = (n - 1) / 26;
        }
        return r;
    }
};

The conversion is backwards (just like binary to decimal and vice versa). You have to minus 1 to account for the fact that ‘A’ equals to 1 not 0.

See also: Teaching Kids Programming – Converting Spreadsheet Column Titles to Number

–EOF (The Ultimate Computing & Technology Blog) —