Tags: , , | Posted by Admin on 5/16/2010 11:19 PM | Comments (0)

Question

Come out with an algorithm for getting the column number provided the column name in a excel sheet and vice versa. Excel has a naming convention of A,B..Z,AA,AB,AC..ZZ,AAA… This had to be converted to the column numbers. A will be 1 and AA will 27.. Also the algorithm to find the name provided column number.

 

Answer

#include <string>
#include <iostream>

using namespace std;

int ExcelColNum (char *name)
{
    int s = 0;
    for (int i=strlen(name)-1, e=1; i>=0; –i, e*=26) {
        s += e*(name[i]-’A'+1);
    }
    return s;
}

string ExcelColName (int num)
{
    string name;
    for ( ; num; num/=26) {
        name.insert (name.begin(), ‘A’+(num-1)%26);
    }
    return name;
}

int main(void)
{
    cout << "AAA ==> " << ExcelColNum ("AAA") << endl;
    cout << "703 ==> " << ExcelColName (703) << endl;

    return 0;
}

 

Results are,

AAA ==> 703
703 ==> AAA

Comments are closed