Algorithm to get the excel-like column name of a number

Question

I'm working on a script that generate some Excel documents and I need to convert a number into its column name equivalent. For example:

1 => A
2 => B
27 => AA
28 => AB
14558 => UMX

I have already written an algorithm to do so, but I'd like to know whether are simpler or faster ways to do it:

function numberToColumnName($number){
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $abc_len = strlen($abc);

    $result_len = 1; // how much characters the column's name will have
    $pow = 0;
    while( ( $pow += pow($abc_len, $result_len) ) < $number ){
        $result_len++;
    }

    $result = "";
    $next = false;
    // add each character to the result...
    for($i = 1; $i<=$result_len; $i++){
        $index = ($number % $abc_len) - 1; // calculate the module

        // sometimes the index should be decreased by 1
        if( $next || $next = false ){
            $index--;
        }

        // this is the point that will be calculated in the next iteration
        $number = floor($number / strlen($abc));

        // if the index is negative, convert it to positive
        if( $next = ($index < 0) ) {
            $index = $abc_len + $index;
        }

        $result = $abc[$index].$result; // concatenate the letter
    }
    return $result;
}

Do you know a better way to do it? Maybe something to keep it simpler? or a performance improvement?

Edit

ircmaxell's implementation works pretty fine. But, I'm going to add this nice short one:

function num2alpha($n)
{
    for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
        $r = chr($n%26 + 0x41) . $r;
    return $r;
}

first comment in this man page might be helpful: http://www.php.net/manual/en/function.base-convert.php#96304 — Sergey Eremin, Jul 21 '10 at 19:17
Have you looked at any of the existing libraries to generate Excel documents from PHP? — Mark Baker, Jul 21 '10 at 21:22
Yes... of course. I'm using your awesome library, Mark. I just like to improve my skills at writing algorithms... what is nice about it is that after you have finished one, you can find other algorithms that does exactly the same but are shorter. — Cristian, Jul 21 '10 at 21:34
Your short algorithm uses 0 -> A rather than 1 -> A in your request, slightly different to the request... if that's what you want, look at PHPExcel's PHPExcel_Cell::stringFromColumnIndex() method, although your num2alpha() implementation might be faster... I'll be running some tests, and may "borrow" it (with permission) — Mark Baker, Jul 21 '10 at 21:41
Results of timing tests: Call time for 1,048,576 iterations of PHPExcel's stringFromColumnIndex() method was 12.9545 seconds. Call time for 1,048,576 iterations of num2Alpha() method was 16.0076 seconds — Mark Baker, Jul 21 '10 at 22:31
Wow... then, I will use your implementation, Mark. Anyway, it was really funny trying to code that algorithm :D — Cristian, Jul 22 '10 at 03:37
Can't guarantee that result: running on a different server - Call time for 1,048,576 iterations of num2alpha() method was 5.6754 seconds; Call time for 1,048,576 iterations of PHPExcel's stringFromColumnIndex() method was 7.7286 seconds — Mark Baker, Jul 22 '10 at 11:37

ircmaxell · Accepted Answer · 2010-07-21T19:30:09.877

174

Here's a nice simple recursive function (Based on zero indexed numbers, meaning 0 == A, 1 == B, etc)...

function getNameFromNumber($num) {
    $numeric = $num % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval($num / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2 - 1) . $letter;
    } else {
        return $letter;
    }
}

And if you want it one indexed (1 == A, etc):

function getNameFromNumber($num) {
    $numeric = ($num - 1) % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval(($num - 1) / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2) . $letter;
    } else {
        return $letter;
    }
}

Tested with numbers from 0 to 10000...

edited Jul 21 '10 at 19:30

answered Jul 21 '10 at 19:23

ircmaxell

163,128
34
264
314

I have translated this PHP script to JS: https://gist.github.com/terox/161db6259e8ddb56dd77 – terox Oct 05 '15 at 10:50
I doubt if recusive function is good or not if used in a loop? Could it cause stack space problem? – Scott Chu Oct 20 '16 at 17:02
it helped me with createing a spreadsheet, where I needed to convert numeric index to letter index for spread sheet column names. – Tuhin Mar 19 '21 at 17:04

ksn135 · Answer 2 · 2018-06-29T16:07:53.157

120

Using PhpSpreadsheet (PHPExcel is deprecated)

// result = 'A'
\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1);

Note index 0 results in 'Z'

https://phpspreadsheet.readthedocs.io/en/develop/

The correct answer (if you use PHPExcel Library) is:

// result = 'A'
$columnLetter = PHPExcel_Cell::stringFromColumnIndex(0); // ZERO-based!

and backwards:

// result = 1
$colIndex = PHPExcel_Cell::columnIndexFromString('A');

edited Jun 29 '18 at 16:07

answered Jun 27 '14 at 19:34

ksn135

1,412
1
11
14

12

stringFromColumnIndex(1) returns 'B' – Ulterior Sep 09 '14 at 07:45
`PHPExcel_Cell::stringFromColumnIndex(1)` indeed returns `'B'`, please edit your answer. – MarthyM Sep 22 '17 at 12:06
1

When using PhpSpreadSheet package, this answer is the best. Remember that PHPExcel is no longer maintained. so use PhpSpreadsheet package instead. – Tuhin Mar 19 '21 at 17:08

Mark Baker · Answer 3 · 2010-07-21T21:51:12.820

13

Indexed for 1 -> A, 2 -> B, etc

function numToExcelAlpha($n) {
    $r = 'A';
    while ($n-- > 1) {
        $r++;
    }
    return $r;
}

Indexed for 0 -> A, 1 -> B, etc

function numToExcelAlpha($n) {
    $r = 'A';
    while ($n-- >= 1) {
        $r++;
    }
    return $r;
}

Takes advantage of the fact that PHP follows Perl's convention when dealing with arithmetic operations on character variables and not C's. Note that character variables can be incremented but not decremented.

edited Jul 21 '10 at 21:51

answered Jul 21 '10 at 21:28

Mark Baker

209,507
32
346
385

it's a good solution but I think it will time out the server on big numbers like 1234567789. – Tahir Raza Aug 19 '19 at 17:48

score 6 · Answer 4 · answered Aug 09 '14 at 02:27

Late answer, but here's what I did (for 1==A indexed):

function num_to_letters($num, $uppercase = true) {
    $letters = '';
    while ($num > 0) {
        $code = ($num % 26 == 0) ? 26 : $num % 26;
        $letters .= chr($code + 64);
        $num = ($num - $code) / 26;
    }
    return ($uppercase) ? strtoupper(strrev($letters)) : strrev($letters);
}

Then if you want to convert the other way:

function letters_to_num($letters) {
    $num = 0;
    $arr = array_reverse(str_split($letters));

    for ($i = 0; $i < count($arr); $i++) {
        $num += (ord(strtolower($arr[$i])) - 96) * (pow(26,$i));
    }
    return $num;
}

Lucas · Answer 5 · 2010-07-22T02:12:38.297

6

This will do to conversion (assuming integer arithmetic), but I agree with the other posters; just use base_convert

function numberToColumnName($number)
{
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $len = strlen($abc);

    $result = "";
    while ($number > 0) {
       $index  = $number % $len;
       $result = $abc[$index] . $result;
       $number = floor($number / $len);
    }

    return $result;
}

edited Jul 22 '10 at 02:12

answered Jul 21 '10 at 19:17

Lucas

8,035
2
32
45

This will return `B` for `$number = 1` instead of `A` since `1 % 26` is `1` – Jalal Apr 05 '20 at 12:59

score 6 · Answer 6 · answered Jun 02 '19 at 19:35

Number convert to Excel column letters:

/**
 * Number convert to Excel column letters
 * 
 * 1 = A
 * 2 = B
 * 3 = C
 * 27 = AA
 * 1234567789 = CYWOQRM
 * 
 * @link https://vector.cool/php-number-convert-to-excel-column-letters-2
 * 
 * @param int  $num       欄數
 * @param bool $uppercase 大小寫
 * @return void
 */
function num_to_letters($n)
{
    $n -= 1;
    for ($r = ""; $n >= 0; $n = intval($n / 26) - 1)
        $r = chr($n % 26 + 0x41) . $r;
    return $r;
}

ex:

echo num_to_letters(1);          // A
echo num_to_letters(2);          // B
echo num_to_letters(3);          // C
echo num_to_letters(27);         // AA
echo num_to_letters(1234567789); // CYWOQRM

Excel column letters convert to Number:

/**
 * Excel column letters convert to Number
 *
 * A = 1
 * B = 2
 * C = 3
 * AA = 27
 * CYWOQRM = 1234567789
 * 
 * @link https://vector.cool/php-number-convert-to-excel-column-letters-2
 * 
 * @param string $letters
 * @return mixed
 */
function letters_to_num($a)
{
    $l = strlen($a);
    $n = 0;
    for ($i = 0; $i < $l; $i++)
        $n = $n * 26 + ord($a[$i]) - 0x40;
    return $n;
}

ex:

echo letters_to_num('A');       // 1
echo letters_to_num('B');       // 2
echo letters_to_num('C');       // 3
echo letters_to_num('AA');      // 27
echo letters_to_num('CYWOQRM'); // 1234567789

Charlie Affumigato · Answer 7 · 2013-01-07T08:56:23.200

Combining ircmaxell's recursive answer I've got this one:


    function getNameFromNumber($num, $index=0) {
        $index = abs($index*1); //make sure index is a positive integer
        $numeric = ($num - $index) % 26; 
        $letter = chr(65 + $numeric);
        $num2 = intval(($num -$index) / 26);
        if ($num2 > 0) {
            return getNameFromNumber($num2 - 1 + $index) . $letter;
        } else {
            return $letter;
        }
    }

I'm using the default indexing as 0 based, but it can be any positive integer for when juggling with arrays in PHP.

score 2 · Answer 8 · answered Jul 21 '10 at 19:27

<?php
function numberToColumnName($number){
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $abc_len = strlen($abc);

    $result = "";
    $tmp = $number;

    while($number > $abc_len) {
        $remainder = $number % $abc_len;
        $result = $abc[$remainder-1].$result;
        $number = floor($number / $abc_len);
    }
    return $abc[$number-1].$result;
}

echo numberToColumnName(1)."\n";
echo numberToColumnName(25)."\n";
echo numberToColumnName(26)."\n";
echo numberToColumnName(27)."\n";
echo numberToColumnName(28)."\n";
echo numberToColumnName(14558)."\n";
?>

Nice try! It will fail though... give it a try with 2049 and you will see :D — Cristian, Jul 21 '10 at 20:35

score 2 · Answer 9 · answered May 09 '18 at 11:26

2

I'd never use this in production because it's not readable, but for fun... Only does up to ZZ.

<?php
    $col = 55;
    print (($n = (int)(($col - 1) / 26)) ? chr($n + 64) : '') . chr((($col - 1) % 26) + 65);
?>

answered May 09 '18 at 11:26

simesy

410
3
9

score 0 · Answer 10 · answered Jun 24 '20 at 11:02

To anyone looking for a Javascript implementation of this, here is @ircmaxell's answer in Javascript..

function getNameFromNumber(num){
    let numeric = num%26;
    let letter = String.fromCharCode(65+numeric);
    let num2 = parseInt(num/26);
    if(num2 > 0) {
      return getNameFromNumber(num2 - 1)+letter;
    } else {
      return letter;
    }
}

score 0 · Answer 11 · answered Sep 05 '21 at 20:06

Here's another impl I just wrote:

function excelColumnName($number) {
    $ordA = ord('A');
    $ordZ = ord('Z');
    $len = $ordZ - $ordA + 1;

    $result = '';
    while($number >= 0) {
        $result = chr($number % $len + $ordA) . $result;
        $number = intval($number/$len) - 1;
    }
    return $result;
}

Some test inputs:

>>> excelColumnName(0)
=> "A"
>>> excelColumnName(1)
=> "B"
>>> excelColumnName(25)
=> "Z"
>>> excelColumnName(26)
=> "AA"
>>> excelColumnName(27)
=> "AB"
>>> excelColumnName(28)
=> "AC"
>>> excelColumnName(52)
=> "BA"
>>> excelColumnName(51)
=> "AZ"

Algorithm to get the excel-like column name of a number

Edit

11 Answers11

Linked

Related