Algorithm to Get the Excel-Like Column Name of a Number

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

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

Javascript Algorithm to get the excel-like column name

In javacript the incrementor operator will return NaN when called on a string value.

You can use ascii code based implementation like

var i3, i4;var text3 = "";
for (i3 = 0; i3 < 26; i3++) {  text3 += String.fromCharCode(97 + i3) + ", ";}for (i3 = 0; i3 < 26; i3++) {  for (i4 = 0; i4 < 26; i4++) {    text3 += String.fromCharCode(97 + i3) + String.fromCharCode(97 + i4) + ", ";  }}

document.getElementById("pId").innerHTML = text3;

<span id="pId"></span>

How do I find the Excel column name that corresponds to a given integer?

I once wrote this function to perform that exact task:

public static string Column(int column)
{
    column--;
    if (column >= 0 && column < 26)
        return ((char)('A' + column)).ToString();
    else if (column > 25)
        return Column(column / 26) + Column(column % 26 + 1);
    else
        throw new Exception("Invalid Column #" + (column + 1).ToString());
}

Programming Riddle: How might you translate an Excel column name to a number?

I wrote this ages ago for some Python script:

def index_to_int(index):
    s = 0
    pow = 1
    for letter in index[::-1]:
        d = int(letter,36) - 9
        s += pow * d
        pow *= 26
    # excel starts column numeration from 1
    return s

How to find the corresponding column number from excel sheet column name in PHP?

function findnum($col){

        $colval = ord($col);
        $len = strlen($col);

        $num = 1;
        if($len==1){
            $num += $colval-65;
        }
        else {
            $flen = $len-1;
            $sec = str_split($col);
            foreach ($sec as $key) {
                $ordval = ord($key);
                $num += ($ordval-65);   
            }
            $num = $num+(26*$flen);

        }
        echo $num;  

    }

What is the algorithm to convert an Excel Column Letter into its Number?

public static int ExcelColumnNameToNumber(string columnName)
{
    if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

    columnName = columnName.ToUpperInvariant();

    int sum = 0;

    for (int i = 0; i < columnName.Length; i++)
    {
        sum *= 26;
        sum += (columnName[i] - 'A' + 1);
    }

    return sum;
}

Number to Excel column name conversion

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

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

---

Related Topics