I was looking for a good way to convert a number to the Excel column letter, for example:
1 -> A
26 -> Z
27 -> AA
28 -> AB
800 -> ADT
etc.
After searching on Google, I could not find any algorithm I want. (The closest one can handle up to two letters, i.e., ZZ. It will crash if the number is too large.). So I ended up creating my own.
Basically, you can think of this problem as a 26 based number conversion. Here is the php version. It should be pretty simple to convert it to a different language. Let me know if you have any problem.
function columnLetter($c){
$c = intval($c);
if ($c < = 0) return '';
$letter = '';
while($c != 0){
$p = ($c - 1) % 26;
$c = intval(($c - $p) / 26);
$letter = chr(65 + $p) . $letter;
}
return $letter;
}
Usage: columnLetter(1303618093); //DERRICK
Note that if you prefer lower cases rather than upper cases, you can simply replace 65 by 96 in the code.
Enjoy~!
–Derrick
Our sponsors:
Thank you!
Thank you so much !! At least THE real algo than can handle more than 2 letters 🙂
I’ve tried to find it by my own, but i failed. Not a real 26 based number in fact, as the “0” digit is skeeped.
Thanks you!
Simple and effective!
Here goes an inverse function of this to convert back from a column letter to a number.
Supports up to 3 letters to col ‘ZZZ’.
function column_number($col){
$col = str_pad($col,3,’0′,STR_PAD_LEFT);
$i = 0;
if ($col{0} != ‘0’) {
$i = ((ord($col{0}) – 64) * 676)+26;
$i += ($col{1} == ‘0’) ? 0 : (ord($col{1}) – 65) * 26;
} else {
$i += ($col{1} == ‘0’) ? 0 : (ord($col{1}) – 64) * 26;
}
$i += ord($col{2}) – 64;
return $i;
}
Based off http://www.bradino.com/php/excel-column-convert-letters-to-numbers/#comment-427148
Undefined variable: letter
Last line of “while” loop… $letter = chr(65 + $p) . $letter;
Last $letter is not defined.
Fixed.
Thanks!
A few moments later…
Thank you! 🙂