Number to Excel column name conversion

user_7195155 0
Viewed 121

In Excel, column names are characters from A to Z, if there are more columns needed, it continues with AA, AB ...

I want to write a function, that converts integers to those excel column names.

0 .... A
25 ... Z
26 ... AA
702 ... AAA
.
.
.

The solution I came up with is working up to AZ, but I want it to work further.

function indexToXlxsColumn($index, $prefix="")
{
    if($index < 26)
    {
        return $prefix.chr($index+65);
    }else{
        return indexToXlxsColumn($index % 26, "A");
    }
}

How to adapt this function to work for every index without producing spaghetti code?

php
4 Answers

Here is the probleme

return indexToXlxsColumn($index % 26, "A");

You away set the next floor prefix to A , what happends when u have input = 53 , the resultat should be "BB"

Code on paper

function indexToXlxsColumn($index, $prefix="")
{
    if($index < 26) // 53 isnt less then 26
                    // second loop , 1 is less then 26 
    {
        return $prefix.chr($index+65);
     // $prefix = A 
     // chr($index+65) = B
     // return "A"."B" ;
    
    }else{ 
        return indexToXlxsColumn($index % 26, "A"); 
        // indexToXlxsColumn(53 % 26, "A") -> (1, "A")
    }
}

---UPDATE---

Follow the ask here is the answer

function indexToXlxsColumn($index, $suffix ="")
{
        if($index < 26){
            return chr($index+65).$suffix ;
        }
        return indexToXlxsColumn(($index - $index%26)/26-1, chr($index%26+65).$suffix );
}
user_105313320

I would do it a little differently. It bothered me to work with chr(). So I once stored the alphabet in a string and iterated over it until the index was successfully resolved.

max "zz"

<?php
function getIn($i) {
    $str = 'abcdefghijklmnopqrstuvwxyz';    
    $r = (int) floor($i / 26) ;
    $c = $i % 26;    
 
    return ($r) < 1 ? $str[$c] : $str[$r-1] .  $str[$c];
}

echo getIn(52); // output: "ba"

Update with max "zzz"

function getIn($i) {
    $str = 'abcdefghijklmnopqrstuvwxyz';    
    
    $r = (int) floor($i / 26);
    $rr = $r >= 27 ? $r - 27 : null;
    $c = $i % 26;

    if ( $rr !== null) {
        return $str[$c] : $str[$r-1] .  $str[$c];    
    }
    return ($r) < 1 ? $str[$c] : $str[$r-1] .  $str[$c];
}

echo getIn(800); // ddu
echo getIn(1377); // zzz
user_148071110

One of the implementations:

function indexToXlxsColumn($index)
{
        $name = '';

        while($index > 0) {
                $mod = ($index - 1) % 26;
                $name = chr(65 + $mod).$name;
                $index = (int)(($index - $mod) / 26);
        }

        return $name;
}


// echo indexToXlxsColumn(26); // Z
echo indexToXlxsColumn(33); // AG
// echo indexToXlxsColumn(800); // ADT
Admin1

PLACEHOLDER parts of the code return squiffy values! Needs review before using in the wild!!

In case anyone wants a utility function that does this, here's a python implementation:

n2AA performs the calculation from numeric to "AA" format.

def n2AA(n):
    n=n-1
    alphabet=["_"] + [chr(c+65) for c in range(0,26)]
    w=len(alphabet)-1
    accum=[]
    for e in range(5,-1,-1):
        expon=w**e
        s=(n//expon)
        r=n%expon
        n=n-(s*expon)
        if e>0:
            accum.append(alphabet[s])
        else:
            accum.append(alphabet[s+1])
    return "".join([a for a in accum if a != "_"])

And AA2n performs the inverse function, starting with "AA" format and returning the column number (starting at 1).

def AA2n(AA):
    alphabet=["_"] + [chr(c+65) for c in range(0,26)]
    w=len(alphabet)-1
    accum=[]
    for c in range(0,len(AA)):
        expon=w**(len(AA)-1-c)
        accum.append((alphabet.index(AA[c]))*expon)
        
    return sum(accum)
user_41370610
Related