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

Viewed   112 times

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;
}
``````

4

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...

Tuesday, August 9, 2022
1

Any optimization done without being measured and profiled first is useless.

PHP code profilers:

• xDebug
• PHP_Debug
• time (Sometimes it is easy to spot bottlenecks in the code using a simple `echo time()`)

Always measure before optimizing!

Tuesday, August 23, 2022
4

Micro-optimisations, but don't do:

``````for (\$p = 0; \$p < sizeof(\$nextStopArray); \$p++) {
...
}
``````

calculate the sizeof(\$nextStopArray) before the loop, otherwise you're doing the count every iteration (and this value isn't being changed)

``````\$nextStopArraySize = sizeof(\$nextStopArray);
for (\$p = 0; \$p < \$nextStopArraySize; ++\$p) {
...
}
``````

There's a couple of places where this should be changed.

And if you're iterating several thousand times, ++\$p is faster than \$p++

But profile the function... find out which parts are taking the longest to execute, and look to optimise those.

EDIT

Get rid of array_push_key as a function, simply execute it inline... it's costing you an unnecessary function call otherwise

Build an array of all nodes from your database outside of the while(true) loop... retrieve all the data in a single SQL query and build a lookup array.

Replacing

``````for (\$p = 0; \$p < sizeof(\$nextStopArray); \$p++) {
``````

with

``````\$nextStopArraySize = sizeof(\$nextStopArray);
\$p = -1
while (++\$p < \$nextStopArraySize) {
...
}
``````

may also prove faster still (just check that the logic does loop through the correct number of times).

Monday, September 5, 2022
4

Your mileage will likely vary on this but for what you have provided you can use a CSS selector combination to target the first link by the page styling.

I use `#search div.r [href*=http]` but you could simplify to `#search .r a`. I am interested in knowing there is an `http` in the `href` though.

The `#` is an id selector, a space `" "` is a descendant selector (selects a child of the preceeding element and the `[]` is an attribute selector. A `"."` is a class selector i.e. selects an element by class name.

I am looking for the first element with an `href` attribute containing `http` in its value that has a parent element `div` element with class name `r`, whose parent has an id of `search`.

``````Option Explicit
Dim ie As New InternetExplorer
With ie
.Visible = True

While .Busy Or .readyState < 4: DoEvents: Wend

Debug.Print .document.querySelector("#search div.r [href*=http]").href

.Quit
End With

End Sub
``````
Tuesday, August 2, 2022
3

To make this value its two's complement, you will have to manipulate the contents. That is of course impossible, so you first get the contents out, manipulate them and then get them into a new `BigInteger`:

``````public static BigInteger twosComplement(BigInteger original)
{
// for negative BigInteger, top byte is negative
byte[] contents = original.toByteArray();

// prepend byte of opposite sign
byte[] result = new byte[contents.length + 1];
System.arraycopy(contents, 0, result, 1, contents.length);
result[0] = (contents[0] < 0) ? 0 : (byte)-1;

// this will be two's complement
return new BigInteger(result);
}

public static void main(String[] args)
{
BigInteger a = new BigInteger("-173B8EC504479C3E95DEB0460411962F9EF2ECE0D3AACD749BE39E1006FC87B8", 16);
BigInteger b = twosComplement(a);

System.out.println(a.toString(16).toUpperCase());
System.out.println(b.toString(16).toUpperCase());

// for comparison, from question:
System.out.println("E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641D61EFF9037848");
}
``````

Output:

``````-173B8EC504479C3E95DEB0460411962F9EF2ECE0D3AACD749BE39E1006FC87B8
E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641C61EFF9037848
E8C4713AFBB863C16A214FB9FBEE69D0610D131F2C55328B641D61EFF9037848
``````

And this new `BigInteger` is really the two's complement, not just a re-interpretation of the bits.

Wednesday, September 28, 2022