What Is the Algorithm to Convert an Excel Column Letter into Its Number

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

Convert Excel column letter to number with Power Query

You should be able to do this with some list functions in a calculated column:

List.Accumulate(
    List.Transform(
        Text.ToList([ColumnLetters]),
        each Character.ToNumber(_) - 64
    ), 0,
    (state, current) => state*26 + current
)

Let's see how this works with an example:

Text.ToList("XFD") = {"X", "F", "D"}

List.Transform({"X", "F", "D"}, each Character.ToNumber(_) - 64)
    = {88-64, 70-64, 68-64}
    = {24, 6, 4}

Note: For the Character.ToNumber offset of 64, you need to be sure the letters are upper case.

List.Accumulate({24, 6, 4}, 0, (state, current) => state*26 + current)
    = ((0*26 + 24)*26 + 6)*26 + 4
    = 4 + 6*26 + 24*26^2
    = 16348

Note: List.Accumulate recursively steps through the list applying the rules you specify at each element.

Convert an excel or spreadsheet column letter to its number in Pythonic fashion

There is a way to make it more pythonic (works with three or more letters and uses less magic numbers):

def col2num(col):
    num = 0
    for c in col:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return num

And as a one-liner using reduce (does not check input and is less readable so I don't recommend it):

col2num = lambda col: reduce(lambda x, y: x*26 + y, [ord(c.upper()) - ord('A') + 1 for c in col])

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

Converting Excel column letters to corresponding column numbers

Note that a character has an ASCII value and you can get the column from the following:

char colstr[]="AEF";
int ii, col=0;
for(ii=0; ii<3; ii++) {
    col = 26*col + colstr[ii] - 'A' + 1;
}

A few things to note:
- I use char[] - a character array - for storing the string. This makes accessing the ASCII value trivial.
- I hard wired the loop to 3 - if your column labels are different lengths you might want to address that
- the value 'A' in single quotes is the character "A" and thus the ASCII value 65.

EDIT: since you seemed to have trouble getting the row value, here is a very simple (C) function that will get you both - together with some code showing a few examples. I couldn't get your code to compile - apparently I don't have the library on my machine.... I am assuming that the label passed to the function is well formed: only letters followed by only numbers. It does handle lowercase letters with the toupper() function (note - this is something your code didn't do...). I figure out where the last letter is, then operate on the two parts. I return the values for row and column in the locations pointed to by row and col parameters.

#include <stdio.h>
#include <stdlib.h>

void rc(char * cellAddr, int *row, int *col) {
  int ii=0, jj, colVal=0;
  while(cellAddr[ii++] >= 'A') {};
  ii--;
  // ii now points to first character of row address
  for(jj=0;jj<ii;jj++) colVal = 26*colVal + toupper(cellAddr[jj]) -'A' + 1;
  *col = colVal;
  *row = atoi(cellAddr+ii);
}

int main() {
    int row, col;
    char cellAddr1[] = "A123";
    char cellAddr2[] = "aB321";
    char cellAddr3[] = "ABCA6543";
    rc(cellAddr1, &row, &col);
    printf("for %s the row is %d and the column is %d\n", cellAddr1, row, col);
    rc(cellAddr2, &row, &col);
    printf("for %s the row is %d and the column is %d\n", cellAddr2, row, col);
    rc(cellAddr3, &row, &col);
    printf("for %s the row is %d and the column is %d\n", cellAddr3, row, col);
}

The output this gives me is:

for A123 the row is 123 and the column is 1
for aB321 the row is 321 and the column is 28
for ABCA6543 the row is 6543 and the column is 19007

How to convert Excel sheet column names into numbers?

This function should work for an arbitrary length column name.

public static int GetColumnNumber(string name)
{
    int number = 0;
    int pow = 1;
    for (int i = name.Length - 1; i >= 0; i--)
    {
        number += (name[i] - 'A' + 1) * pow;
        pow *= 26;
    }

    return number;
}

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

Explanation of algorithm that get the excel column title

It is, in effect, "converting" the column number to base 26, where the "digits" are the letters A..Z.

For example, for column 720:

• modulo = (720-1)%26 = 17
• columnName = 'R'
• dividend = (720-17)/26 = 27
• modulo = (27-1)%26 = 0
• columnName = A+columnName = AR
• dividend = (27-0)/26 = 1
• modulo = (1-1)%26 = 0
• columnName = A + columnName = AAR
• dividend = (1-0)/26 = 0

Resulting in AAR.

---

Related Topics