How to convert a column number (eg. 127) into an e

Question

How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.

Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.

Answer 1

I discovered an error in my first post, so I decided to sit down and do the the math. What I found is that the number system used to identify Excel columns is not a base 26 system, as another person posted. Consider the following in base 10. You can also do this with the letters of the alphabet.

Space:.........................S1, S2, S3 : S1, S2, S3
....................................0, 00, 000 :.. A, AA, AAA
....................................1, 01, 001 :.. B, AB, AAB
.................................... …, …, … :.. …, …, …
....................................9, 99, 999 :.. Z, ZZ, ZZZ
Total states in space: 10, 100, 1000 : 26, 676, 17576
Total States:...............1110................18278

Excel numbers columns in the individual alphabetical spaces using base 26. You can see that in general, the state space progression is a, a^2, a^3, … for some base a, and the total number of states is a + a^2 + a^3 + … .

Suppose you want to find the total number of states A in the first N spaces. The formula for doing so is A = (a)(a^N - 1 )/(a-1). This is important because we need to find the space N that corresponds to our index K. If I want to find out where K lies in the number system I need to replace A with K and solve for N. The solution is N = log{base a} (A (a-1)/a +1). If I use the example of a = 10 and K = 192, I know that N = 2.23804… . This tells me that K lies at the beginning of the third space since it is a little greater than two.

The next step is to find exactly how far in the current space we are. To find this, subtract from K the A generated using the floor of N. In this example, the floor of N is two. So, A = (10)(10^2 – 1)/(10-1) = 110, as is expected when you combine the states of the first two spaces. This needs to be subtracted from K because these first 110 states would have already been accounted for in the first two spaces. This leaves us with 82 states. So, in this number system, the representation of 192 in base 10 is 082.

The C# code using a base index of zero is

    private string ExcelColumnIndexToName(int Index)
    {
        string range = string.Empty;
        if (Index < 0 ) return range;
        int a = 26;
        int x = (int)Math.Floor(Math.Log((Index) * (a - 1) / a + 1, a));
        Index -= (int)(Math.Pow(a, x) - 1) * a / (a - 1);
        for (int i = x+1; Index + i > 0; i--)
        {
            range = ((char)(65 + Index % a)).ToString() + range;
            Index /= a;
        }
        return range;
    }

//Old Post

A zero-based solution in C#.

    private string ExcelColumnIndexToName(int Index)
    {
        string range = "";
        if (Index < 0 ) return range;
        for(int i=1;Index + i > 0;i=0)
        {
            range = ((char)(65 + Index % 26)).ToString() + range;
            Index /= 26;
        }
        if (range.Length > 1) range = ((char)((int)range[0] - 1)).ToString() + range.Substring(1);
        return range;
    }

Answer 2

After looking at all the supplied Versions here, i descided to do one myself, using recursion.

Here is my vb.net Version:

Function CL(ByVal x As Integer) As String
    If x >= 1 And x <= 26 Then
        CL = Chr(x + 64)
    Else
        CL = CL((x - x Mod 26) / 26) & Chr((x Mod 26) + 1 + 64)
    End If
End Function

Answer 3

Here's my super late implementation in PHP. This one's recursive. I wrote it just before I found this post. I wanted to see if others had solved this problem already...

public function GetColumn($intNumber, $strCol = null) {

    if ($intNumber > 0) {
        $intRem = ($intNumber - 1) % 26;
        $strCol = $this->GetColumn(intval(($intNumber - $intRem) / 26), sprintf('%s%s', chr(65 + $intRem), $strCol));
    }

    return $strCol;
}

Answer 4

Just throwing in a simple two-line C# implementation using recursion, because all the answers here seem far more complicated than necessary.

/// <summary>
/// Gets the column letter(s) corresponding to the given column number.
/// </summary>
/// <param name="column">The one-based column index. Must be greater than zero.</param>
/// <returns>The desired column letter, or an empty string if the column number was invalid.</returns>
public static string GetColumnLetter(int column) {
    if (column < 1) return String.Empty;
    return GetColumnLetter((column - 1) / 26) + (char)('A' + (column - 1) % 26);
}

Answer 5

private String getColumn(int c) {
    String s = "";
    do {
        s = (char)('A' + (c % 26)) + s;
        c /= 26;
    } while (c-- > 0);
    return s;
}

Its not exactly base 26, there is no 0 in the system. If there was, 'Z' would be followed by 'BA' not by 'AA'.

Answer 6

JavaScript Solution

/**
 * Calculate the column letter abbreviation from a 1 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
        value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};