Convert excel column to equivalent number

Tags: ,

Convert excel column to equivalent number

Every column is assigned a value, A =1 , B=2 … and Z= 26. Again AA = 27, AB = 28…. and AZ = 52. Given a string S, find out the equivalent number. For example for AAA it will be 703.

This problem has simple solution. We just need to come up with the mathematical formula for it. So let’s work out some examples so that we can identify some pattern and then figure out formula. A  = 1 = 1 * (26 ^0) AB = 2 + 26 ^1 =  28 AAB = 2 + 26^1 + 26^2 = 704  B  = 2 = 2 * (26 ^0) BB = 2 + 2 * (26^1) =  54 ABB = 2 + 2* (26^1) + 1 *( 26^2) = 730 C = 3  = 3 * (26 ^0)  AC  = 3 * (26^0) + 1 * (26^1) = 29 ACC = 3 * (26^0) + 3 * (26^1) + 1 * (26^2)=757

Do you see some pattern?  Yes, we need to get the power of 26 based on the place the character is in string , starting from right hand side. For example, in ABB, we took (26^2) because A is at second position from the right side, starting from zero. What else? We multiply that power to the value assigned to that character. For example, for C we multiply with 3. Value of single character can be easily found out using char -‘A’ + 1 expression. Rest is to just add each of the value to get the result. Hence our formula is
result  = result + S[i] * power(26, len-i-1) where i = 0 to len-1
It is fairly simple to code it.

int power(int i){

   if(i==0)
      return 1;
   if(i == 1) return 26;
   else{
        int temp =  power(i/2);
        if(i%2){
              return 26 * temp *temp;
        }
        else
              return temp * temp;
   }
}
void excel_column(char *s){

 int i ;
 int len  = strlen(s);
 int rank =0;
 for(i=0; i<len;i++){
        rank += power(len-i-1) * ((s[i]-'A')+1);
 }
 printf("%d", rank);
}

The complexity to convert excel column to an equivalent number code is O(N) where N is the size of the string.