StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAlgorithm to get the excel-like column name of a number
text
Body
copied!<p>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:</p> <pre><code>1 => A 2 => B 27 => AA 28 => AB 14558 => UMX </code></pre> <p>I have already written an algorithm to do so, but I'd like to know whether are simpler or faster ways to do it:</p> <pre><code>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; } </code></pre> <p>Do you know a better way to do it? Maybe something to keep it simpler? or a performance improvement?</p> <h3>Edit</h3> <p>ircmaxell's implementation works pretty fine. But, I'm going to add this nice short one:</p> <pre><code>function num2alpha($n) { for($r = ""; $n >= 0; $n = intval($n / 26) - 1) $r = chr($n%26 + 0x41) . $r; return $r; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload