How to convert a column number (e.g. 127) into an Excel column (e.g. 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

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 2

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

=SUBSTITUTE(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 3

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

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 4

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 5

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 6

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 7

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 8

This answer is in javaScript:

function getCharFromNumber(columnNumber){
    var dividend = columnNumber;
    var columnName = "";
    var modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo).toString() + columnName;
        dividend = parseInt((dividend - modulo) / 26);
    } 
    return  columnName;
}

Answer 9

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 10

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

I'm surprised all of the solutions so far contain either iteration or recursion.

Here's my solution that runs in constant time (no loops). This solution works for all possible Excel columns and checks that the input can be turned into an Excel column. Possible columns are in the range [A, XFD] or [1, 16384]. (This is dependent on your version of Excel)

private static string Turn(uint col)
{
    if (col < 1 || col > 16384) //Excel columns are one-based (one = 'A')
        throw new ArgumentException("col must be >= 1 and <= 16384");

    if (col <= 26) //one character
        return ((char)(col + 'A' - 1)).ToString();

    else if (col <= 702) //two characters
    {
        char firstChar = (char)((int)((col - 1) / 26) + 'A' - 1);
        char secondChar = (char)(col % 26 + 'A' - 1);

        if (secondChar == '@') //Excel is one-based, but modulo operations are zero-based
            secondChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}", firstChar, secondChar);
    }

    else //three characters
    {
        char firstChar = (char)((int)((col - 1) / 702) + 'A' - 1);
        char secondChar = (char)((col - 1) / 26 % 26 + 'A' - 1);
        char thirdChar = (char)(col % 26 + 'A' - 1);

        if (thirdChar == '@') //Excel is one-based, but modulo operations are zero-based
            thirdChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}{2}", firstChar, secondChar, thirdChar);
    }
}

Answer 12

Same implementation in Java

public String getExcelColumnName (int columnNumber) 
    {     
        int dividend = columnNumber;   
        int i;
        String columnName = "";     
        int modulo;     
        while (dividend > 0)     
        {        
            modulo = (dividend - 1) % 26;         
            i = 65 + modulo;
            columnName = new Character((char)i).toString() + columnName;        
            dividend = (int)((dividend - modulo) / 26);    
        }       
        return columnName; 
    }  

Answer 13

I wanted to throw in my static class I use, for interoping between col index and col Label. I use a modified accepted answer for my ColumnLabel Method

public static class Extensions
{
    public static string ColumnLabel(this int col)
    {
        var dividend = col;
        var columnLabel = string.Empty;
        int modulo;

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

        return columnLabel;
    }
    public static int ColumnIndex(this string colLabel)
    {
        // "AD" (1 * 26^1) + (4 * 26^0) ...
        var colIndex = 0;
        for(int ind = 0, pow = colLabel.Count()-1; ind < colLabel.Count(); ++ind, --pow)
        {
            var cVal = Convert.ToInt32(colLabel[ind]) - 64; //col A is index 1
            colIndex += cVal * ((int)Math.Pow(26, pow));
        }
        return colIndex;
    }
}

Use this like...

30.ColumnLabel(); // "AD"
"AD".ColumnIndex(); // 30

Answer 14

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 15

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 16

In Delphi (Pascal):

function GetExcelColumnName(columnNumber: integer): string;
var
  dividend, modulo: integer;
begin
  Result := '';
  dividend := columnNumber;
  while dividend > 0 do begin
    modulo := (dividend - 1) mod 26;
    Result := Chr(65 + modulo) + Result;
    dividend := (dividend - modulo) div 26;
  end;
end;

Answer 17

A little late to the game, but here's the code I use (in C#):

private static readonly string _Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
public static int ColumnNameParse(string value)
{
    // assumes value.Length is [1,3]
    // assumes value is uppercase
    var digits = value.PadLeft(3).Select(x => _Alphabet.IndexOf(x));
    return digits.Aggregate(0, (current, index) => (current * 26) + (index + 1));
}

Answer 18

In perl, for an input of 1 (A), 27 (AA), etc.

sub excel_colname {
  my ($idx) = @_;       # one-based column number
  --$idx;               # zero-based column index
  my $name = "";
  while ($idx >= 0) {
    $name .= chr(ord("A") + ($idx % 26));
    $idx   = int($idx / 26) - 1;
  }
  return scalar reverse $name;
}

Answer 19

After looking at all the supplied Versions here, I decided 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 20

Though I am late to the game, Graham's answer is far from being optimal. Particularly, you don't have to use the modulo, call ToString() and apply (int) cast. Considering that in most cases in C# world you would start numbering from 0, here is my revision:

public static string GetColumnName(int index) // zero-based
{
    const byte BASE = 'Z' - 'A' + 1;
    string name = String.Empty;

    do
    {
        name = Convert.ToChar('A' + index % BASE) + name;
        index = index / BASE - 1;
    }
    while (index >= 0);

    return name;
}

Answer 21

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 22

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 23

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

Answer 24

These my codes to convert specific number (index start from 1) to Excel Column.

    public static string NumberToExcelColumn(uint number)
    {
        uint originalNumber = number;

        uint numChars = 1;
        while (Math.Pow(26, numChars) < number)
        {
            numChars++;

            if (Math.Pow(26, numChars) + 26 >= number)
            {
                break;
            }               
        }

        string toRet = "";
        uint lastValue = 0;

        do
        {
            number -= lastValue;

            double powerVal = Math.Pow(26, numChars - 1);
            byte thisCharIdx = (byte)Math.Truncate((columnNumber - 1) / powerVal);
            lastValue = (int)powerVal * thisCharIdx;

            if (numChars - 2 >= 0)
            {
                double powerVal_next = Math.Pow(26, numChars - 2);
                byte thisCharIdx_next = (byte)Math.Truncate((columnNumber - lastValue - 1) / powerVal_next);
                int lastValue_next = (int)Math.Pow(26, numChars - 2) * thisCharIdx_next;

                if (thisCharIdx_next == 0 && lastValue_next == 0 && powerVal_next == 26)
                {
                    thisCharIdx--;
                    lastValue = (int)powerVal * thisCharIdx;
                }
            }

            toRet += (char)((byte)'A' + thisCharIdx + ((numChars > 1) ? -1 : 0));

            numChars--;
        } while (numChars > 0);

        return toRet;
    }

My Unit Test:

    [TestMethod]
    public void Test()
    {
        Assert.AreEqual("A", NumberToExcelColumn(1));
        Assert.AreEqual("Z", NumberToExcelColumn(26));
        Assert.AreEqual("AA", NumberToExcelColumn(27));
        Assert.AreEqual("AO", NumberToExcelColumn(41));
        Assert.AreEqual("AZ", NumberToExcelColumn(52));
        Assert.AreEqual("BA", NumberToExcelColumn(53));
        Assert.AreEqual("ZZ", NumberToExcelColumn(702));
        Assert.AreEqual("AAA", NumberToExcelColumn(703));
        Assert.AreEqual("ABC", NumberToExcelColumn(731));
        Assert.AreEqual("ACQ", NumberToExcelColumn(771));
        Assert.AreEqual("AYZ", NumberToExcelColumn(1352));
        Assert.AreEqual("AZA", NumberToExcelColumn(1353));
        Assert.AreEqual("AZB", NumberToExcelColumn(1354));
        Assert.AreEqual("BAA", NumberToExcelColumn(1379));
        Assert.AreEqual("CNU", NumberToExcelColumn(2413));
        Assert.AreEqual("GCM", NumberToExcelColumn(4823));
        Assert.AreEqual("MSR", NumberToExcelColumn(9300));
        Assert.AreEqual("OMB", NumberToExcelColumn(10480));
        Assert.AreEqual("ULV", NumberToExcelColumn(14530));
        Assert.AreEqual("XFD", NumberToExcelColumn(16384));
    }

Answer 25

Another VBA way

Public Function GetColumnName(TargetCell As Range) As String
    GetColumnName = Split(CStr(TargetCell.Cells(1, 1).Address), "$")(1)
End Function

Answer 26

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 27

I'm trying to do the same thing in Java... I've wrote following code:

private String getExcelColumnName(int columnNumber) {

    int dividend = columnNumber;
    String columnName = "";
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;

        char val = Character.valueOf((char)(65 + modulo));

        columnName += val;

        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

Now once I ran it with columnNumber = 29, it gives me the result = "CA" (instead of "AC") any comments what I'm missing? I know I can reverse it by StringBuilder.... But looking at the Graham's answer, I'm little confused....

Answer 28

Coincise and elegant Ruby version:

def col_name(col_idx)
    name = ""
    while col_idx>0
        mod     = (col_idx-1)%26
        name    = (65+mod).chr + name
        col_idx = ((col_idx-mod)/26).to_i
    end
    name
end

Answer 29

This is the question all others as well as Google redirect to so I'm posting this here.

Many of these answers are correct but too cumbersome for simple situations such as when you don't have over 26 columns. If you have any doubt whether you might go into double character columns then ignore this answer, but if you're sure you won't, then you could do it as simple as this in C#:

public static char ColIndexToLetter(short index)
{
    if (index < 0 || index > 25) throw new ArgumentException("Index must be between 0 and 25.");
    return (char)('A' + index);
}

Heck, if you're confident about what you're passing in you could even remove the validation and use this inline:

(char)('A' + index)

This will be very similar in many languages so you can adapt it as needed.

Again, only use this if you're 100% sure you won't have more than 26 columns.

Answer 30

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

def excel_column_number_to_name(column_number):
    output = ""
    index = column_number-1
    while index >= 0:
        character = chr((index%26)+ord('A'))
        output = output + character
        index = index/26 - 1

    return output[::-1]


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

Passed these test cases:

• Column Number: 494286 => ABCDZ
• Column Number: 27 => AA
• Column Number: 52 => AZ