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

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

int nCol = 127;
string sChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol >= 26)
{
    int nChar = nCol % 26;
    nCol = (nCol - nChar) / 26;
    // You could do some trick with using nChar as offset from 'A', but I am lazy to do it right now.
    sCol = sChars[nChar] + sCol;
}
sCol = sChars[nCol] + sCol;

Update: Peter's comment is right. That's what I get for writing code in the browser. :-) My solution was not compiling, it was missing the left-most letter and it was building the string in reverse order - all now fixed.

Bugs aside, the algorithm is basically converting a number from base 10 to base 26.

Update 2: Joel Coehoorn is right - the code above will return AB for 27. If it was real base 26 number, AA would be equal to A and the next number after Z would be BA.

int nCol = 127;
string sChars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol > 26)
{
    int nChar = nCol % 26;
    if (nChar == 0)
        nChar = 26;
    nCol = (nCol - nChar) / 26;
    sCol = sChars[nChar] + sCol;
}
if (nCol != 0)
    sCol = sChars[nCol] + sCol;

Answer 2

Isn't this just base 26?

string colName = "";
while ( colID >= 26 ) {
// note PRE-pending the next character
    colName = ((colID % 26)+'A') + colName;
    colID /= 26;
}
return colName;

optimized:

// Hmmm, added char casts to avoid, and initial string to force
// string concatenation operator.  Not sure if this'll fix the C# issues, but
// hope so.
colName = ""+
          (((char)(( colID > 26*26*26 ) ? (( colID / (26*26*26) ))) + 'A') : "") +
          (((char)( colID > 26*26 ) ? (( colID / (26*26) ) + 'A'))) : "") +
          (((char)( colID > 26 ) ? (( colID / 26 ) + 'A') : ""))) +
          (((char)( colID % 26 )) + 'A');

Answer 3

Easy with recursion.

public static string GetStandardExcelColumnName(int columnNumberOneBased)
{
  int baseValue = Convert.ToInt32('A');
  int columnNumberZeroBased = columnNumberOneBased - 1;

  string ret = "";

  if (columnNumberOneBased > 26)
  {
    ret = GetStandardExcelColumnName(columnNumberZeroBased / 26) ;
  }

  return ret + Convert.ToChar(baseValue + (columnNumberZeroBased % 26) );
}

Answer 4

Not forgetting that there are limits in the number of columns available.

E.g.

• Excel 2003 (v11) goes up to IV, 2^8 or 256 columns).
• Excel 2007 (v12) goes up to XFD, 2^14 or 16384 columns.

Answer 5

Sorry, this is Python instead of C#, but at least the results are correct:

def ColIdxToXlName(idx):
    if idx < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if idx > 26:
            idx, r = divmod(idx - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(idx + ord('A') - 1) + result


for i in xrange(1, 1024):
    print "%4d : %s" % (i, ColIdxToXlName(i))

Answer 6

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

Answer 7

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 8

I'm using this one in VB.NET 2003 and it works well...

Private Function GetExcelColumnName(ByVal aiColNumber As Integer) As String
    Dim BaseValue As Integer = Convert.ToInt32(("A").Chars(0)) - 1
    Dim lsReturn As String = String.Empty

    If (aiColNumber > 26) Then
        lsReturn = GetExcelColumnName(Convert.ToInt32((Format(aiColNumber / 26, "0.0").Split("."))(0)))
    End If

    GetExcelColumnName = lsReturn + Convert.ToChar(BaseValue + (aiColNumber Mod 26))
End Function

Answer 9

If you are wanting to reference the cell progmatically then you will get much more readable code and much better performance if you use the Cells method of a sheet. It takes a row and column index instead of a traditonal cell reference. It is very similar to the Offset method.

Answer 10

If anyone needs to do this in Excel without VBA, here is a way:

=SUBSTITUTE(TEXT(ADDRESS(1;colNum;4);"");"1";"")

where colNum is the column number

And in VBA:

Function GetColumnName(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

Answer 11

Using this in VB.Net 2005 :

Private Function ColumnName(ByVal ColumnIndex As Integer) As String

   Dim Name As String = ""

   Name = (New Microsoft.Office.Interop.Owc11.Spreadsheet).Columns.Item(ColumnIndex).Address(False, False, Microsoft.Office.Interop.Owc11.XlReferenceStyle.xlA1)
   Name = Split(Name, ":")(0)

   Return Name

End Function

Answer 12

Refining the original solution (in C#):

public static class ExcelHelper
{
    private static Dictionary<UInt16, String> l_DictionaryOfColumns;

    public static ExcelHelper() {
        l_DictionaryOfColumns = new Dictionary<ushort, string>(256);
    }

    public static String GetExcelColumnName(UInt16 l_Column)
    {
        UInt16 l_ColumnCopy = l_Column;
        String l_Chars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        String l_rVal = "";
        UInt16 l_Char;


        if (l_DictionaryOfColumns.ContainsKey(l_Column) == true)
        {
            l_rVal = l_DictionaryOfColumns[l_Column];
        }
        else
        {
            while (l_ColumnCopy > 26)
            {
                l_Char = l_ColumnCopy % 26;
                if (l_Char == 0)
                    l_Char = 26;

                l_ColumnCopy = (l_ColumnCopy - l_Char) / 26;
                l_rVal = l_Chars[l_Char] + l_rVal;
            }
            if (l_ColumnCopy != 0)
                l_rVal = l_Chars[l_ColumnCopy] + l_rVal;

            l_DictionaryOfColumns.ContainsKey(l_Column) = l_rVal;
        }

        return l_rVal;
    }
}

Answer 13

Another solution:

private void Foo()
{
   l_ExcelApp = new Excel.ApplicationClass();
   l_ExcelApp.ReferenceStyle = Excel.XlReferenceStyle.xlR1C1;
   // ... now reference by R[row]C[column], Ex. A1 <==> R1C1, C6 <==> R3C6, ...
}

see more here - Cell referencing in Excel for everyone! by Dr Nitin Paranjape

Answer 14

..And converted to php:

function GetExcelColumnName($columnNumber) {
    $columnName = '';
    while ($columnNumber > 0) {
     $modulo = ($columnNumber - 1) % 26;
     $columnName = chr(65 + $modulo) . $columnName;
     $columnNumber = (int)(($columnNumber - $modulo) / 26);
    }
    return $columnName;
}

Answer 15

public static string ConvertToAlphaColumnReferenceFromInteger(int columnReference)
    {
        int baseValue = ((int)('A')) - 1 ;
        string lsReturn = String.Empty; 

        if (columnReference > 26) 
        {
            lsReturn = ConvertToAlphaColumnReferenceFromInteger(Convert.ToInt32(Convert.ToDouble(columnReference / 26).ToString().Split('.')[0]));
        } 

        return lsReturn + Convert.ToChar(baseValue + (columnReference % 26));            
    }

Answer 16

Here is an Actionscript version:

private var columnNumbers:Array = ['A', 'B', 'C', 'D', 'E', 'F' , 'G', 'H', 'I', 'J', 'K' ,'L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

    private function getExcelColumnName(columnNumber:int) : String{
        var dividend:int = columnNumber;
        var columnName:String = "";
        var modulo:int;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = columnNumbers[modulo] + columnName;
            dividend = int((dividend - modulo) / 26);
        } 

        return columnName;
    }

Answer 17

You might need conversion both ways, e.g from Excel column adress like AAZ to integer and from any integer to Excel. The two methods below will do just that. Assumes 1 based indexing, first element in your "arrays" are element number 1. No limits on size here, so you can use adresses like ERROR and that would be column number 2613824 ...

enter code here

public static string ColumnAdress(int col) { if (col <= 26) { return Convert.ToChar(col + 64).ToString(); } int div = col / 26; int mod = col % 26; if (mod == 0) {mod = 26;div--;} return ColumnAdress(div) + ColumnAdress(mod); }

public static int ColumnNumber(string colAdress)
{
  int[] digits = new int[colAdress.Length];
  for (int i = 0; i < colAdress.Length; ++i)
  {
    digits[i] = Convert.ToInt32(colAdress[i]) - 64;
  }
  int mul=1;int res=0;
  for (int pos = digits.Length - 1; pos >= 0; --pos)
  {
    res += digits[pos] * mul;
    mul *= 26;
  }
  return res;
}

Answer 18

@Arent Arntzen, What changes would need to be made to convert to zero-based indexing? I'm using NPOI and columns are zero-based.

Answer 19

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 20

if you just want it for a cell formula without code, here's a formula for it:

IF(COLUMN()>=26,CHAR(ROUND(COLUMN()/26,1)+64)&CHAR(MOD(COLUMN(),26)+64),CHAR(COLUMN()+64))

Answer 21

If anyone is interested, here is the reverse of the problem: how to convert a column name to the column number. This is written in VBA.

Public Function Base26To10(Base26 As String) As Long

   Dim lngLen As Long
   Dim lngCurrIteration As Long
   Dim lngNumber As Long
   Dim lngAsc As Long
   Dim i As Long

   lngLen = Len(Base26)

   For i = lngLen To 1 Step -1
      lngAsc = Asc(Mid$(Base26, i, 1))
      If lngAsc < 65 Or lngAsc > 90 Then
         Base26To10 = 0
         Exit Function
      End If
      lngNumber = lngNumber + ((26 ^ lngCurrIteration) * (lngAsc - 64))
      lngCurrIteration = lngCurrIteration + 1
   Next i

   Base26To10 = lngNumber

End Function