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.
If you are wanting to reference the cell progmatically then you will get much more readable code 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.
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.
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;
}
public string ToBase26(int number)
{
if (number < 0) return String.Empty;
int remainder = number % 26;
int value = number / 26;
return value == 0 ?
String.Format("{0}", Convert.ToChar(65 + remainder)) :
String.Format("{0}{1}", ToBase26(value - 1), Convert.ToChar(65 + remainder));
}
This is a common question asked in coding test. it has some constraints: max columns per row= 702 output should have row number+column name e.g. for 703 answer is 2A. (note: i have just modified existing code from another answer) here is the code for the same:
static string GetExcelColumnName(long columnNumber)
{
//max number of column per row
const long maxColPerRow = 702;
//find row number
long rowNum = (columnNumber / maxColPerRow);
//find tierable columns in the row.
long dividend = columnNumber - (maxColPerRow * rowNum);
string columnName = String.Empty;
long modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
return rowNum+1+ columnName;
}
}
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;
}
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));
}
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.
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'.
(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());
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
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
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.
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))
Typescript
function lengthToExcelColumn(len: number): string {
let dividend: number = len;
let columnName: string = '';
let modulo: number = 0;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo).toString() + columnName;
dividend = Math.floor((dividend - modulo) / 26);
}
return columnName;
}
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)
Here is a simpler solution for zero based column Index
public static string GetColumnIndexNumberToExcelColumn(int columnIndex)
{
int offset = columnIndex % 26;
int multiple = columnIndex / 26;
int initialSeed = 65;//Represents column "A"
if (multiple == 0)
{
return Convert.ToChar(initialSeed + offset).ToString();
}
return $"{Convert.ToChar(initialSeed + multiple - 1)}{Convert.ToChar(initialSeed + offset)}";
}
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;
}
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
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 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).
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;
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
// 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
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) );
}
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
Same implementaion 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;
}
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;
}
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'.
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) );
}
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:
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;
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;
}
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;
}
}
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
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))
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;
Here is my solution in python
import math
num = 3500
row_number = str(math.ceil(num / 702))
letters = ''
num = num - 702 * math.floor(num / 702)
while num:
mod = (num - 1) % 26
letters += chr(mod + 65)
num = (num - 1) // 26
result = row_number + ("".join(reversed(letters)))
print(result)
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));
}
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);
}
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;
}
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;
}
}
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
Typescript
function lengthToExcelColumn(len: number): string {
let dividend: number = len;
let columnName: string = '';
let modulo: number = 0;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo).toString() + columnName;
dividend = Math.floor((dividend - modulo) / 26);
}
return columnName;
}
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))
If you are wanting to reference the cell progmatically then you will get much more readable code 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.
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;
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;
}
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;
};
This is a common question asked in coding test. it has some constraints: max columns per row= 702 output should have row number+column name e.g. for 703 answer is 2A. (note: i have just modified existing code from another answer) here is the code for the same:
static string GetExcelColumnName(long columnNumber)
{
//max number of column per row
const long maxColPerRow = 702;
//find row number
long rowNum = (columnNumber / maxColPerRow);
//find tierable columns in the row.
long dividend = columnNumber - (maxColPerRow * rowNum);
string columnName = String.Empty;
long modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
return rowNum+1+ columnName;
}
}
Here is my solution in python
import math
num = 3500
row_number = str(math.ceil(num / 702))
letters = ''
num = num - 702 * math.floor(num / 702)
while num:
mod = (num - 1) % 26
letters += chr(mod + 65)
num = (num - 1) // 26
result = row_number + ("".join(reversed(letters)))
print(result)
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
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))
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....
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:
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.
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())
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) );
}
Same implementaion 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;
}
Another VBA way
Public Function GetColumnName(TargetCell As Range) As String
GetColumnName = Split(CStr(TargetCell.Cells(1, 1).Address), "$")(1)
End Function
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
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
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;
}
If you are wanting to reference the cell progmatically then you will get much more readable code 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.
T-SQL (SQL SERVER 18)
Copy of the solution on first page
CREATE FUNCTION dbo.getExcelColumnNameByOrdinal(@RowNum int)
RETURNS varchar(5)
AS
BEGIN
DECLARE @dividend int = @RowNum;
DECLARE @columnName varchar(max) = '';
DECLARE @modulo int;
WHILE (@dividend > 0)
BEGIN
SELECT @modulo = ((@dividend - 1) % 26);
SELECT @columnName = CHAR((65 + @modulo)) + @columnName;
SELECT @dividend = CAST(((@dividend - @modulo) / 26) as int);
END
RETURN
@columnName;
END;
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;
}
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) );
}
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
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 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).
Seems like so many answers are much more complex than necessary. Here is a generic Ruby answer based on the recursion described above:
One nice thing about this answer is that it's not limited to the 26 characters of English Alphabet. You can define any range you like in COLUMNS
constant and it will do the right thing.
# vim: ft=ruby
class Numeric
COLUMNS = ('A'..'Z').to_a
def to_excel_column(n = self)
n < 1 ? '' : begin
base = COLUMNS.size
to_excel_column((n - 1) / base) + COLUMNS[(n - 1) % base]
end
end
end
# verify:
(1..52).each { |i| printf "%4d => %4s\n", i, i.to_excel_column }
This prints the following, eg:
1 => A
2 => B
3 => C
....
33 => AG
34 => AH
35 => AI
36 => AJ
37 => AK
38 => AL
39 => AM
40 => AN
41 => AO
42 => AP
43 => AQ
44 => AR
45 => AS
46 => AT
47 => AU
48 => AV
49 => AW
50 => AX
51 => AY
52 => AZ
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
More than 30 solutions already, but here's my one-line C# solution...
public string IntToExcelColumn(int i)
{
return ((i<16926? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<2730? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<26? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + ((char)((i%26)+65)));
}
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
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;
}
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.
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())
..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;
}
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);
}
}
(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());
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;
}
Seems like so many answers are much more complex than necessary. Here is a generic Ruby answer based on the recursion described above:
One nice thing about this answer is that it's not limited to the 26 characters of English Alphabet. You can define any range you like in COLUMNS
constant and it will do the right thing.
# vim: ft=ruby
class Numeric
COLUMNS = ('A'..'Z').to_a
def to_excel_column(n = self)
n < 1 ? '' : begin
base = COLUMNS.size
to_excel_column((n - 1) / base) + COLUMNS[(n - 1) % base]
end
end
end
# verify:
(1..52).each { |i| printf "%4d => %4s\n", i, i.to_excel_column }
This prints the following, eg:
1 => A
2 => B
3 => C
....
33 => AG
34 => AH
35 => AI
36 => AJ
37 => AK
38 => AL
39 => AM
40 => AN
41 => AO
42 => AP
43 => AQ
44 => AR
45 => AS
46 => AT
47 => AU
48 => AV
49 => AW
50 => AX
51 => AY
52 => AZ
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;
}
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;
}
T-SQL (SQL SERVER 18)
Copy of the solution on first page
CREATE FUNCTION dbo.getExcelColumnNameByOrdinal(@RowNum int)
RETURNS varchar(5)
AS
BEGIN
DECLARE @dividend int = @RowNum;
DECLARE @columnName varchar(max) = '';
DECLARE @modulo int;
WHILE (@dividend > 0)
BEGIN
SELECT @modulo = ((@dividend - 1) % 26);
SELECT @columnName = CHAR((65 + @modulo)) + @columnName;
SELECT @dividend = CAST(((@dividend - @modulo) / 26) as int);
END
RETURN
@columnName;
END;
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;
};
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;
}
Saw one other VBA answer - this can be done in excel-vba with a 1 line UDF:
Function GetColLetter(ByVal colID As Integer) As String
GetColLetter = Split(Cells(1, colID).Address, "$")(1)
End Function
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;
};
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
More than 30 solutions already, but here's my one-line C# solution...
public string IntToExcelColumn(int i)
{
return ((i<16926? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<2730? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<26? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + ((char)((i%26)+65)));
}
Saw one other VBA answer - this can be done in excel-vba with a 1 line UDF:
Function GetColLetter(ByVal colID As Integer) As String
GetColLetter = Split(Cells(1, colID).Address, "$")(1)
End Function
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
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));
}
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
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
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));
}
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
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
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);
}
}
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))
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;
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'.
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));
}
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
// 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
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))
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;
};
public string ToBase26(int number)
{
if (number < 0) return String.Empty;
int remainder = number % 26;
int value = number / 26;
return value == 0 ?
String.Format("{0}", Convert.ToChar(65 + remainder)) :
String.Format("{0}{1}", ToBase26(value - 1), Convert.ToChar(65 + remainder));
}
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;
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
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.
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;
}
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
Here is a simpler solution for zero based column Index
public static string GetColumnIndexNumberToExcelColumn(int columnIndex)
{
int offset = columnIndex % 26;
int multiple = columnIndex / 26;
int initialSeed = 65;//Represents column "A"
if (multiple == 0)
{
return Convert.ToChar(initialSeed + offset).ToString();
}
return $"{Convert.ToChar(initialSeed + multiple - 1)}{Convert.ToChar(initialSeed + offset)}";
}
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));
}
..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;
}
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;
}
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)
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);
}
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
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
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'.
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;
}
Source: Stackoverflow.com