By robertkroll


2008-10-08 06:55:01 8 Comments

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.

30 comments

@grepit 2018-10-29 05:26:51

Most of previous answers are correct. Here is one more way of converting column number to excel columns. solution is rather simple if we think about this as a base conversion. Simply, convert the column number to base 26 since there is 26 letters only. Here is how you can do this:

steps:

  • set the column as a quotient

  • subtract one from quotient variable (from previous step) because we need to end up on ascii table with 97 being a.

  • divide by 26 and get the remainder.

  • add +97 to remainder and convert to char (since 97 is "a" in ASCII table)
  • quotient becomes the new quotient/ 26 (since we might go over 26 column)
  • continue to do this until quotient is greater than 0 and then return the result

here is the code that does this :)

def convert_num_to_column(column_num):
    result = ""
    quotient = column_num
    remainder = 0
    while (quotient >0):
        quotient = quotient -1
        remainder = quotient%26
        result = chr(int(remainder)+97)+result
        quotient = int(quotient/26)
    return result

print("--",convert_num_to_column(1).upper())

@user1098928 2018-10-13 06:14:15

For what it is worth, here is Graham's code in Powershell:

function ConvertTo-ExcelColumnID {
param (
    [parameter(Position = 0,
        HelpMessage = "A 1-based index to convert to an excel column ID. e.g. 2 => 'B', 29 => 'AC'",
        Mandatory = $true)]
    [int]$index
);

[string]$result = '';
if ($index -le 0 ) {
    return $result;
}

while ($index -gt 0) {
    [int]$modulo = ($index - 1) % 26;
    $character = [char]($modulo + [int][char]'A');
    $result = $character + $result;
    [int]$index = ($index - $modulo) / 26;
}

return $result;

}

@Kevin 2018-08-12 03:30:29

This is a javascript version according to Graham's code

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

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

    return columnName;
};

@Extragorey 2017-01-24 00:01:56

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

@phlare 2017-09-11 22:52:09

Thanks for the answers here!! helped me come up with these helper functions for some interaction with the Google Sheets API that i'm working on in Elixir/Phoenix

here's what i came up with (could probably use some extra validation and error handling)

In Elixir:

def number_to_column(number) do
  cond do
    (number > 0 && number <= 26) ->
      to_string([(number + 64)])
    (number > 26) ->
      div_col = number_to_column(div(number - 1, 26))
      remainder = rem(number, 26)
      rem_col = cond do
        (remainder == 0) ->
          number_to_column(26)
        true ->
          number_to_column(remainder)
      end
      div_col <> rem_col
    true ->
      ""
  end
end

And the inverse function:

def column_to_number(column) do
  column
    |> to_charlist
    |> Enum.reverse
    |> Enum.with_index
    |> Enum.reduce(0, fn({char, idx}, acc) ->
      ((char - 64) * :math.pow(26,idx)) + acc
    end)
    |> round
end

And some tests:

describe "test excel functions" do
  @excelTestData [{"A", 1}, {"Z",26}, {"AA", 27}, {"AB", 28}, {"AZ", 52},{"BA", 53}, {"AAA", 703}]

  test "column to number" do
    Enum.each(@excelTestData, fn({input, expected_result}) ->
      actual_result = BulkOnboardingController.column_to_number(input)
      assert actual_result == expected_result
    end)
  end

  test "number to column" do
    Enum.each(@excelTestData, fn({expected_result, input}) ->
      actual_result = BulkOnboardingController.number_to_column(input)
      assert actual_result == expected_result
    end)
  end
end

@DataEngineer 2017-11-05 18:04:45

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

@Vlad Schnakovszki 2017-03-22 11:01:04

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.

@Maslow 2016-12-14 17:42:02

F# version of each way

let rec getExcelColumnName x  = if x<26 then int 'A'+x|>char|>string else (x/26-1|>c)+ c(x%26)

pardon the minimizing, was working on a better version of https://stackoverflow.com/a/4500043/57883


and the opposite direction:

// return values start at 0
let getIndexFromExcelColumnName (x:string) =
    let a = int 'A'
    let fPow len i =
        Math.Pow(26., len - 1 - i |> float)
        |> int

    let getValue len i c = 
        int c - a + 1 * fPow len i
    let f i = getValue x.Length i x.[i]
    [0 .. x.Length - 1]
    |> Seq.map f
    |> Seq.sum
    |> fun x -> x - 1

@user2792497 2016-08-26 15:54:15

Microsoft Excel Miniature, Quick-and-Dirty formula.

Hi,

Here's one way to get the Excel character-column-header from a number....

I created a formula for an Excel cell.

(i.e. I took the approach of not using VBA programming.)

The formula looks at a cell that has a number in it and tells you what the column is -- in letters.

In the attached image:

  • I put 1,2,3 etc in the top row all the way out to column ABS.
  • I pasted my formula in the second row all the way out to ABS.
  • My formula looks at row 1 and converts the number to Excel's column header id.
  • My formula works for all numbers out to 702 (zz).
  • I did it in this manner to prove that the formula works so you can look at the output from the formula and look at the column header above and easily visually verify that the formula works. :-)

    =CONCATENATE(MID("_abcdefghijklmnopqrstuvwxyz",(IF(MOD(K1,26)>0,INT(K1/26)+1,(INT(K1/26)))),1),MID("abcdefghijklmnopqrstuvwxyz",IF(MOD(K1,26)=0,26,MOD(K1,26)),1))

The underscore was there for debugging purposes - to let you know there was an actual space and that it was working correctly.

With this formula above -- whatever you put in K1 - the formula will tell you what the column header will be.

The formula, in its current form, only goes out to 2 digits (ZZ) but could be modified to add the 3rd letter (ZZZ).

enter image description here

@Herman Kan 2016-08-18 12:34:21

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

@Sam 2015-11-13 22:45:06

Saw one other VBA answer - this can be done in with a 1 line UDF:

Function GetColLetter(ByVal colID As Integer) As String
    GetColLetter = Split(Cells(1, colID).Address, "$")(1)
End Function

@Rob Sawyer 2015-08-09 01:44:26

NodeJS implementation:

/**
* getColumnFromIndex
* Helper that returns a column value (A-XFD) for an index value (integer).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa/3444285#3444285
* @param numVal: Integer
* @return String
*/
getColumnFromIndex: function(numVal){
   var dividend = parseInt(numVal);
   var columnName = '';
   var modulo;
   while (dividend > 0) {
      modulo = (dividend - 1) % 26;
      columnName = String.fromCharCode(65 + modulo) + columnName;
      dividend = parseInt((dividend - modulo) / 26);
   }
   return columnName;
},

Thanks to Convert excel column alphabet (e.g. AA) to number (e.g., 25). And in reverse:

/**
* getIndexFromColumn
* Helper that returns an index value (integer) for a column value (A-XFD).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/9905533/convert-excel-column-alphabet-e-g-aa-to-number-e-g-25
* @param strVal: String
* @return Integer
*/
getIndexFromColumn: function(val){
   var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;
   for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
      result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
   }
   return result;
}

@bpolat 2015-08-01 00:33:11

Objective-C Implementation :

-(NSString*)getColumnName:(int)n {
     NSString *name = @"";
     while (n>0) {
     n--;
     char c = (char)('A' + n%26);
     name = [NSString stringWithFormat:@"%c%@",c,name];
     n = n/26;
  }    
     return name;

}

SWIFT Implementation:

func getColumnName(n:Int)->String{
 var columnName = ""
 var index = n
 while index>0 {
     index--
     let char = Character(UnicodeScalar(65 + index%26))
     columnName = "\(char)\(columnName)"
     index = index / 26
 }
 return columnName

}

The answer is based on :https://stackoverflow.com/a/4532562/2231118

@Madison Tobal 2015-06-18 10:51:23

I just had to do this work today, my implementation uses recursion:

private static string GetColumnLetter(string colNumber)
{
    if (string.IsNullOrEmpty(colNumber))
    {
        throw new ArgumentNullException(colNumber);
    }

    string colName = String.Empty;

    try
    {
        var colNum = Convert.ToInt32(colNumber);
        var mod = colNum % 26;
        var div = Math.Floor((double)(colNum)/26);
        colName = ((div > 0) ? GetColumnLetter((div - 1).ToString()) : String.Empty) + Convert.ToChar(mod + 65);
    }
    finally
    {
        colName = colName == String.Empty ? "A" : colName;
    }

    return colName;
}

This considers the number coming as string the the method and the numbers starting in "0" (A = 0)

@Myforwik 2012-04-18 01:42:57

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

@Iwan B. 2015-04-24 11:38:11

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

@S_R 2015-04-09 22:35:33

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

@t3dodson 2014-09-26 15:33:16

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

@Paul Ma 2014-02-21 02:29:21

Another VBA way

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

@Hasan 2012-02-24 14:45:36

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

@mcalex 2013-03-28 05:51:45

Graham says: columnName = Convert.ToChar(65 + modulo).ToString() + columnName (ie value + ColName). Hasan says: columnName += val; (ie ColName + value)

@Arent Arntzen 2010-04-16 12:25:09

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

@vzczc 2009-01-30 14:25:33

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

@Anonymous Type 2010-07-22 23:29:21

+1 for providing a VBA alternative.

@Dolph 2010-10-13 18:55:46

Example: =SUBSTITUTE(TEXT(ADDRESS(1,1000,4),""),"1","")

@vzczc 2010-10-14 07:05:06

Yes, I use Excel in a locale where ; is used in place of , to separate function arguments in Excel. Thanks for pointing this out.

@dayuloli 2014-01-28 18:49:35

I did this without the TEXT function. What is the purpose of the TEXT function?

@vzczc 2014-01-29 09:03:43

@dayuloli Long time since this answer was written, you are correct, the TEXT function does not serve a purpose here. Will update the answer.

@Ian 2014-01-24 10:21:52

(I realise the question relates to C# however, if anyone reading needs to do the same thing with Java then the following may be useful)

It turns out that this can easily be done using the the "CellReference" class in Jakarta POI. Also, the conversion can be done both ways.

// Convert row and column numbers (0-based) to an Excel cell reference
CellReference numbers = new CellReference(3, 28);
System.out.println(numbers.formatAsString());

// Convert an Excel cell reference back into digits
CellReference reference = new CellReference("AC4");
System.out.println(reference.getRow() + ", " + reference.getCol());

@Ally 2013-10-03 09:43:58

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

@Eric 2015-07-10 21:12:47

Try index 26 and 27. It's very close, but off by one.

@Henry Liu 2019-04-12 09:49:48

value = Math.floor(value / 26); should be value = Math.ceil(value / 26) - 1;

@Gordon Freeman 2013-05-01 22:44:13

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

@MaVRoSCy 2013-03-28 10:06:58

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

@user2023861 2013-02-08 19:36: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);
    }
}

@bernard paulus 2013-07-10 14:25:13

FYI: @Graham's answer (and probably the others) are more general than yours: they support 4+ characters in the column names. And that's precisely why they are iterative.

@bernard paulus 2013-07-10 14:25:58

In fact, if they used unlimited integers and not ints, the resulting column name could be arbitrarily long (that's the case of the python answer, for instance)

@user2023861 2013-07-10 14:45:58

If my data is ever too large for a 16,384-column spreadsheet, I'll shoot myself in the head. Anyways, Excel doesn't even support all of the possible three-letter columns (it cuts off at XFD leaving out 1,894 columns). Right now anyways. I'll update my answer in the future as required.

@bernard paulus 2013-07-10 15:17:21

:) didn't knew that! My comment was on the theoretical properties of the different algorithms.

@Juan 2018-07-11 06:24:50

This one is probably the simplest and clearest solution.

@Kelly L 2012-01-05 07:38:50

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

@nurettin 2012-02-28 12:00:27

You did the inverse of what was asked, but +1 for your lambda-fu.

@Vlad 2012-05-25 08:48:15

IndexOf is quite slow, you'd better precalcuate the reverse mapping.

@user932708 2011-10-13 08:09:30

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

@lambdapilgrim 2011-10-11 15:45:16

Here is how I would do it in Python. The algorithm is explained below:

alph = ('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')
def labelrec(n, res):
    if n<26:
        return alph[n]+res
    else:
        rem = n%26
        res = alph[rem]+res
        n = n/26-1
        return labelrec(n, res)

The function labelrec can be called with the number and an empty string like:

print labelrec(16383, '')

Here is why it works: If decimal numbers were written the same way as Excel sheet columns, number 0-9 would be written normally, but 10 would become '00' and then 20 would become '10' and so on. Mapping few numbers:

0 - 0

9 - 9

10 - 00

20 - 10

100 - 90

110 - 000

1110 - 0000

So, the pattern is clear. Starting at the unit's place, if a number is less than 10, it's representation is same as the number itself, else you need to adjust the remaining number by subtracting it by 1 and recurse. You can stop when the number is less than 10.

The same logic is applied for numbers of base 26 in above solution.

P.S. If you want the numbers to begin from 1, call the same function on input number after decreasing it by 1.

Related Questions

Sponsored Content

29 Answered Questions

[SOLVED] How do I generate a random int number?

  • 2010-04-24 23:09:11
  • Rella
  • 1995244 View
  • 1679 Score
  • 29 Answer
  • Tags:   c# random

13 Answered Questions

27 Answered Questions

[SOLVED] How do I enumerate an enum in C#?

9 Answered Questions

[SOLVED] What are the correct version numbers for C#?

39 Answered Questions

[SOLVED] How do I properly clean up Excel interop objects?

25 Answered Questions

[SOLVED] Is it possible to force Excel recognize UTF-8 CSV files automatically?

  • 2011-05-14 13:53:39
  • Lyubomyr Shaydariv
  • 432967 View
  • 379 Score
  • 25 Answer
  • Tags:   excel csv utf-8

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 997859 View
  • 1732 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

26 Answered Questions

[SOLVED] Why not inherit from List<T>?

9 Answered Questions

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

  • 2009-03-20 20:14:42
  • David Basarab
  • 36701 View
  • 57 Score
  • 9 Answer
  • Tags:   c# excel math

20 Answered Questions

[SOLVED] Import and Export Excel - What is the best library?

Sponsored Content