## Example PHP code to convert a number to an Excel column letter

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

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

## 8 Replies to “Example PHP code to convert a number to an Excel column letter”

1. excellent

Thank you!

2. Anonymous

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.

3. Alejandro

Thanks you!
Simple and effective!

4. Brent Kelly

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){
\$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;
}

5. Carlos Diaz

Undefined variable: letter

Last line of “while” loop… \$letter = chr(65 + \$p) . \$letter;
Last \$letter is not defined.

• Derrick Post author

Fixed.

6. Rafael

Thanks!

7. Gosling

A few moments later…

Thank you! 🙂