CareerCup

This can be done Recursively -

string excelColumnName(int col)
{
string colName = "";

//assume we have a hashtable with "keys 0-25, and a-z as corresponding values"
if(col<26){
return AlphaValue[col];
}
else {
string p1 = excelColumnName(col % 26); //mod by 26
string p2 = excelColumnName(col / 26 - 1); //since numbers in our program start from 0 and
//not 1
return p2 + p1;
}
}

Very few solutions in this post actually work. There is a subtle 'trick' to this question. At first glance many of you are assuming this is simply a atoi/itoa with a base of 26 but that's not correct. The letters A->Z represent 0->25 only if the A is in the rightmost position of the string. For all other positions A->Z represent 1->26 and therefore the code has to handle this appropriately.

Nitin's 'itoa' solution above works due to the subtle trick where the while conditional is

while(n>=0)

and the divide equals statement is:

n = n/26-1

What this means is that on the first iteration, it treats it purely as a base 26 conversion (modulo 26 then divide/equals 26, next iteration. However for all subsequent iterations the -1 on the divide/equals statement shifts A=1...Z=26. Then finally the generated string is reversed to get it to be in the correct order.

Here is the atoi in ruby:

def col_string_to_num(string_in)
  raise ArgumentError, "Input int cannot be nil or empty" if string_in.nil? || string_in.length.zero?

  raise ArgumentError, "Input string must be composed of [A-Z]*" if string_in != string_in.upcase || !all_letters(string_in)

  retval = 0
  len = string_in.length
  (0..len-1).each do |i|
    if i == len - 1
      retval += (string_in[i].ord - 'A'.ord) * (26**(len-i-1))
    else
      retval += (string_in[i].ord - 'A'.ord+1) * (26**(len-i-1))
    end
  end

  retval
end

Here is my solution in Ruby:

def convert_column(num)
	nums = [*0..25]
	alphabet = [*'A'..'Z']
	letter_map = Hash[ nums.zip(alphabet) ]
	result = ''

	until num == -1
		remainder = num % 26
		result += letter_map[remainder]
		num = (num / 26) - 1
	end

	result.reverse
end

Here is one written in C++

void excel_column_converter(std::string& source)
{
  bool is_alpha = false;

  if (source[0] >= 'A' && source[0] <= 'Z' )
  { 
    is_alpha = true;
  } else if (source[0] >= '0' && source[0] <= '9')
  {       
    is_alpha = false;
  } else {
    std::cout<<"invalid input" <<std::endl;
  }          
  if (!is_alpha) 
  {
    std::string result = "";
    char *pEnd;
    long number = atoi(source.c_str());
    
    int residual = 0;
    do {
      residual = number%26;
      char next = 'A'+residual; 
      result = std::string(&next, 1) + result;  
      if (number >= 26 && ((number/26) <26))
      {
        char last = number/26;
        next = 'A' + (last-1);
        result = std::string(&next, 1) + result;
      }
      number /= 26;
    } while(number >= 26);
    std::cout<< "result for "<<source<< "=" <<result <<std::endl;
  } else {
    int i = 0, sum = 0;
    while(i < source.length() && source[i]>= 'A' && source[i] <= 'Z')
    {
      sum = sum*25 + ((source[i] - 'A')+1);
      i++;
    }
    std::cout<<"result for "<< source<<"="<< sum<<std::endl;
  }
}

void rev(char s[])
{
     int len = strlen(s)-1;
     int i=0;
     while(i<len)
     {
         s[i]=s[i]^s[len];
         s[len]=s[i]^s[len];
         s[i]=s[i]^s[len];
         i++;
         len--;
     }
}

void convert(int n)
{
     char s[100];
     int i=0;
     while(n>=0)
     {
        s[i++]='A' + n%26;
        n= n/26-1;
     }
     s[i]='\0';
     rev(s);
     printf("%s\n",s);
}

void change(char s[])
{
     int i=0,n=0;
     while(s[i]!='\0')
         n = n + i*26 + s[i++]-'A';
     printf("%d\n",n);
}

Not sure why the "and reverse" part, but here is some c# that did the trick. I chose to hardcode the alphabet vs. char lookup as it seemed like the less interesting part of the problem.

protected String ExcelColumnLettersFromNumber(double col)
    {
        StringBuilder sb = new StringBuilder();
        string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        col++;

        double pow = 0;
        while (Math.Pow(26,pow) < col)
            pow++;

        pow--;
        while (pow >= 0) {
            int numSets = Convert.ToInt16(col/Math.Pow(26,pow));
            col = col % Math.Pow(26,pow--);
            // Insert does the "reverse" part for us inline
            sb.Insert(0,String.Format("{0}",alphabet[numSets-1]));
        }
        return sb.ToString();
    }

My previous example did not save whitespace doing again -

string excelColumnName(int col) 
{ 
string colName = ""; 

//assume we have a hashtable with "keys 0-25, and a-z as corresponding values" 
if(col<26){ 
return AlphaValue[col]; 
} 
else { 
string p1 = excelColumnName(col % 26); //mod by 26 
string p2 = excelColumnName(col / 26 - 1); //since numbers in our program start from 0 and 
//not 1 
return p2 + p1; 
} 
}

public String convertToString(int n){
if(n<0)
return null;

StringBuffer sb= new StringBuffer();
while(n>=0){
char c=n%26+65; //integer to char
n=n/26-1;
sb= (new StringBuffer(c)).append(sb);
}

return sb.toString();
}

This can take any number
public String getExcelColumn(int colNo){
if( colNo > 25){
int remainingCOlNo = ((int)colNo / 26) -1;
int modColNo = colNo % 26;
return getExcelColumn(remainingCOlNo)+alphabet.charAt(modColNo);
}else{
return alphabet.charAt(colNo)+"";
}
}

public class ExcelColumn {

	
	static boolean isValid(String valueEntered)
	{
		if( null == valueEntered || valueEntered.isEmpty() )
			return false ;
		
		int number = -1 ;
		try {
			number = Integer.parseInt(valueEntered);
		} catch (NumberFormatException e) {
			System.out.println("Error parsing number");
		}
		if (number < 0 )
			return false;
		
		return true;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		Console scan = System.console();
		System.out.println("Enter number to convert : ");
		String valueEntered = scan.readLine();
		
		if( ! isValid(valueEntered) )
		{
			System.out.println("Input is invalid. Please enter number. Exiting ...");
			System.exit(-1);
		}
		
		int intColumn = Integer.parseInt(valueEntered);
		
		StringBuilder column = new StringBuilder(16);
		int reminder, division;
		while ( intColumn >= 0 )
		{
			reminder = intColumn % 26 ;
			intColumn = intColumn / 26 - 1 ;
			column.append(chars.charAt(reminder));
		}
		System.out.println(column.reverse());
		
	}

}

ArrayList<Character> returnColumnNameFromCode(int code){
		if(code==0){
			return null; 
		}
		ArrayList<Character> charCode=new ArrayList<Character>();
		for(int i=1;code>0;i=i*26){
			int val=code%26;
			char thisChar;
			if(val==0){
				thisChar='A';
			}
			else{
				thisChar=(char)(val+64);
			}
			charCode.add(thisChar);
			if(i==1){
				code=code-val;
			}
			else{
				code=code-val-i;
			}
			
		}
		return charCode;
	}

char captialA = '63', since you know its '63' already you can use the ascii as a place holder to get A-Z, and then just offset accordingly to get B, C, D, E.

so conversion numeric to char yields
First character position = ((num / 26) - 1) +'63' = A
Second character = ((num % 26) + '63' = A

char back numeric position would be
second character = convert A back to ascii = 63 - subtract - 'A' (63) = 0
second character is just simple add.
First character = convert A back to ascii = subtract - 'A' (63) = 0
multiple by 26 to get quantity and add to the second character to get full value.

#include "string.h"
#define RADIX 26
void printVal(int code){
    if(code<RADIX){
        if(code != 0){
            cout<<(char)('A'+code -1);
        }else{
            cout<<(char)('A'+code);
        }
        return;
    }    
    printVal(code/RADIX);
    cout<<(char)('A' + code%RADIX);
}

void printCode(char* val){
    int len = strlen(val);
    int code= 0;
    for(int i=0;i<len-1;i++){
        code+=(RADIX*(val[i]-'A'+1));
    }
    code+=(val[len-1]-'A');
    cout<<code<<"\n";
}

int main(){

    int code = 353;
    char val[] = "MP";
    printVal(code);
    printCode(val);
    return 0;
}

Here is my solution in Ruby:

def convert_column(num)
	nums = [*0..25]
	alphabet = [*'A'..'Z']
	letter_map = Hash[nums.zip(alphabet)]
	result = ''

	until num == -1
		remainder = num % 26
		result += letter_map[remainder]
		num = (num / 26) - 1
	end

	result.reverse
end

public static String getAlphaValue(int num) {
String result = "";
while (num > 0) {
num--; // 1 => a, not 0 => a
int remainder = num % 26;
char digit = (char) (remainder + 65);
result = digit + result;
num = (num - remainder) / 26;
}
return result;
}

public static String getAlpha(int num) {
        String result = "";
        while (num > 0) {
          num--; // 1 => a, not 0 => a
          int remainder = num % 26;
          char digit = (char) (remainder + 65);
          result = digit + result;
          num = (num - remainder) / 26;
        }
        return result;
     }

public static String getAlpha(int num) {

String result = "";

while (num > 0) {

num--;

int remainder = num % 26;

char digit = (char) (remainder + 65);

result = digit + result;

num = (num - remainder) / 26;

}

return result;

}

public static String getAlpha(int num) {
        String result = "";
        while (num > 0) {
          num--; // 1 => a, not 0 => a
          int remainder = num % 26;
          char digit = (char) (remainder + 65);
          result = digit + result;
          num = (num - remainder) / 26;
        }
        return result;
     }

F**K YOU spammer.

F**K U if post another link to your blog.

Here is the code with nice explanation.

onestopinterviewprep.blogspot.com/2014/03/excel-column-number-to-alpha-and-reverse.html

@Anonymous, if you are not interested , just don't click on the link or don't see my posts. It's as simple as that.