'Translate a column index into an Excel Column Name
Given a columns' index, how can you get an Excel column name?
The problem is trickier than it sounds because it's not just base-26. The columns don't wrap over like normal digits would. Even the Microsoft Support Example doesn't scale beyond ZZZ.
Disclaimer: This is some code I had done a while back, and it came across my desktop again today. I thought it was worthy of posting here as a pre-answered question.
Solution 1:[1]
Here's Joel's awesome code modified to work with zero-based column indexes and without the char array.
Public Shared Function GetExcelColumn(ByVal index As Integer) As String
Dim quotient As Integer = index \ 26 ' Truncate
If quotient > 0 Then
Return GetExcelColumn(quotient - 1) & Chr((index Mod 26) + 64).ToString
Else
Return Chr(index + 64).ToString
End If
End Function
Solution 2:[2]
It's for this very reason that I avoid column names in programmed interface to Excel. Using column numbers works very well in Cell(r,c) references and R1C1 addressing.
EDIT: The Range function also takes cell references, as in Range(Cell(r1,c1),Cell(r2,c2)). Also, you can use the Address function to get the A1-style address of a cell or range.
EDIT2: Here's a VBA function that uses the Address() function to retrieve the column name:
Function colname(colindex)
x = Cells(1, colindex).Address(False, False) ' get the range name (e.g. AB1)
colname = Mid(x, 1, Len(x) - 1) ' return all but last character
End Function
Solution 3:[3]
public static String translateColumnIndexToName(int index) {
//assert (index >= 0);
int quotient = (index)/ 26;
if (quotient > 0) {
return translateColumnIndexToName(quotient-1) + (char) ((index % 26) + 65);
} else {
return "" + (char) ((index % 26) + 65);
}
}
and the test:
for (int i = 0; i < 100; i++) {
System.out.println(i + ": " + translateColumnIndexToName(i));
}
here is the output:
0: A
1: B
2: C
3: D
4: E
5: F
6: G
7: H
8: I
9: J
10: K
11: L
12: M
13: N
14: O
15: P
16: Q
17: R
18: S
19: T
20: U
21: V
22: W
23: X
24: Y
25: Z
26: AA
27: AB
28: AC
I needed 0 based for POI
and translation from index to names:
public static int translateComunNameToIndex0(String columnName) {
if (columnName == null) {
return -1;
}
columnName = columnName.toUpperCase().trim();
int colNo = -1;
switch (columnName.length()) {
case 1:
colNo = (int) columnName.charAt(0) - 64;
break;
case 2:
colNo = ((int) columnName.charAt(0) - 64) * 26 + ((int) columnName.charAt(1) - 64);
break;
default:
//illegal argument exception
throw new IllegalArgumentException(columnName);
}
return colNo;
}
Solution 4:[4]
# Python 2.x, no recursive function calls
def colname_from_colx(colx):
assert colx >= 0
colname = ''
r = colx
while 1:
r, d = divmod(r, 26)
colname = chr(d + ord('A')) + colname
if not r:
return colname
r -= 1
Solution 5:[5]
This is an old post, but after seeing some of the solutions I came up with my own C# variation. 0-Based, without recursion:
public static String GetExcelColumnName(int columnIndex)
{
if (columnIndex < 0)
{
throw new ArgumentOutOfRangeException("columnIndex: " + columnIndex);
}
Stack<char> stack = new Stack<char>();
while (columnIndex >= 0)
{
stack.Push((char)('A' + (columnIndex % 26)));
columnIndex = (columnIndex / 26) - 1;
}
return new String(stack.ToArray());
}
Here are some test results at key transition points:
0: A
1: B
2: C
...
24: Y
25: Z
26: AA
27: AB
...
50: AY
51: AZ
52: BA
53: BB
...
700: ZY
701: ZZ
702: AAA
703: AAB
Solution 6:[6]
in python, with recursion. translated from Joey's answer. so far, it's tested to work up to GetExcelByColumn(35) = 'AI'
def GetExcelColumn(index):
quotient = int(index / 26)
if quotient > 0:
return GetExcelColumn(quotient) + str(chr((index % 26) + 64))
else:
return str(chr(index + 64))
Solution 7:[7]
The php version, thank's to this post to help me figure it out ! ^^
/**
* Get excel column name
* @param index : a column index we want to get the value in excel column format
* @return (string) : excel column format
*/
function getexcelcolumnname($index) {
//Get the quotient : if the index superior to base 26 max ?
$quotient = $index / 26;
if ($quotient >= 1) {
//If yes, get top level column + the current column code
return getexcelcolumnname($quotient-1). chr(($index % 26)+65);
} else {
//If no just return the current column code
return chr(65 + $index);
}
}
Solution 8:[8]
JavaScript Solution
/**
* Calculate the column letter abbreviation from a 0 based index
* @param {Number} value
* @returns {string}
*/
getColumnFromIndex = function (value) {
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
value++;
var remainder, result = "";
do {
remainder = value % 26;
result = base[(remainder || 26) - 1] + result;
value = Math.floor(value / 26);
} while (value > 0);
return result;
};
Solution 9:[9]
I enjoy writing recursive functions, but I don't think it's necessary here. This is my solution in VB. It works up to column ZZ. If someone can tell me if it works for AAA to ZZZ that would be nice to know.
Public Function TranslateColumnIndexToName(index As Integer) As String
'
Dim remainder As Integer
Dim remainder2 As Integer
Dim quotient As Integer
Dim quotient2 As Integer
'
quotient2 = ((index) / (26 * 26)) - 2
remainder2 = (index Mod (26 * 26)) - 1
quotient = ((remainder2) / 26) - 2
remainder = (index Mod 26) - 1
'
If quotient2 > 0 Then
TranslateColumnIndexToName = ChrW(quotient2 + 65) & ChrW(quotient + 65) & ChrW(remainder + 65)
ElseIf quotient > 0 Then
TranslateColumnIndexToName = ChrW(quotient + 65) & ChrW(remainder + 65)
Else
TranslateColumnIndexToName = ChrW(remainder + 65)
End If
End Function
Solution 10:[10]
Here is my solution in C#
// test
void Main()
{
for( var i = 0; i< 1000; i++ )
{ var byte_array = code( i );
Console.WriteLine("{0} | {1} | {2}", i, byte_array, offset(byte_array));
}
}
// Converts an offset to AAA code
public string code( int offset )
{
List<byte> byte_array = new List<byte>();
while( offset >= 0 )
{
byte_array.Add( Convert.ToByte(65 + offset % 26) );
offset = offset / 26 - 1;
}
return ASCIIEncoding.ASCII.GetString( byte_array.ToArray().Reverse().ToArray());
}
// Converts AAA code to an offset
public int offset( string code)
{
var offset = 0;
var byte_array = Encoding.ASCII.GetBytes( code ).Reverse().ToArray();
for( var i = 0; i < byte_array.Length; i++ )
{
offset += (byte_array[i] - 65 + 1) * Convert.ToInt32(Math.Pow(26.0, Convert.ToDouble(i)));
}
return offset - 1;
}
Solution 11:[11]
Here is my answer in C#, for translating both ways between column index and column name.
/// <summary>
/// Gets the name of a column given the index, as it would appear in Excel.
/// </summary>
/// <param name="columnIndex">The zero-based column index number.</param>
/// <returns>The name of the column.</returns>
/// <example>Column 0 = A, 26 = AA.</example>
public static string GetColumnName(int columnIndex)
{
if (columnIndex < 0) throw new ArgumentOutOfRangeException("columnIndex", "Column index cannot be negative.");
var dividend = columnIndex + 1;
var columnName = string.Empty;
while (dividend > 0)
{
var modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo) + columnName;
dividend = (dividend - modulo) / 26;
}
return columnName;
}
/// <summary>
/// Gets the zero-based column index given a column name.
/// </summary>
/// <param name="columnName">The column name.</param>
/// <returns>The index of the column.</returns>
public static int GetColumnIndex(string columnName)
{
var index = 0;
var total = 0;
for (var i = columnName.Length - 1; i >= 0; i--)
total += (columnName.ToUpperInvariant()[i] - 64) * (int)Math.Pow(26, index++);
return total - 1;
}
Solution 12:[12]
In Ruby:
class Fixnum
def col_name
quot = self/26
(quot>0 ? (quot-1).col_name : "") + (self%26+65).chr
end
end
puts 0.col_name # => "A"
puts 51.col_name # => "AZ"
Solution 13:[13]
This JavaScript version shows that at its core it's a conversion to base 26:
function colName(x)
{
x = (parseInt("ooooooop0", 26) + x).toString(26);
return x.slice(x.indexOf('p') + 1).replace(/./g, function(c)
{
c = c.charCodeAt(0);
return String.fromCharCode(c < 64 ? c + 17 : c - 22);
});
}
The .toString(26) bit shows that Joel Coehoorn is wrong: it is a simple base conversion.
(Note: I have a more straight-forward implementation based on Dana's answer in production. It's less heavy, works for larger numbers although that won't affect me, but also doesn't show the mathematical principle as clearly.)
P.S. Here's the function evaluated at important points:
0 A
1 B
9 J
10 K
24 Y
25 Z
26 AA
27 AB
700 ZY
701 ZZ
702 AAA
703 AAB
18276 ZZY
18277 ZZZ
18278 AAAA
18279 AAAB
475252 ZZZY
475253 ZZZZ
475254 AAAAA
475255 AAAAB
12356628 ZZZZY
12356629 ZZZZZ
12356630 AAAAAA
12356631 AAAAAB
321272404 ZZZZZY
321272405 ZZZZZZ
321272406 AAAAAAA
321272407 AAAAAAB
8353082580 ZZZZZZY
8353082581 ZZZZZZZ
8353082582 AAAAAAAA
8353082583 AAAAAAAB
Solution 14:[14]
this is with Swift 4 :
@IBAction func printlaction(_ sender: Any) {
let textN : Int = Int (number_textfield.text!)!
reslut.text = String (printEXCL_Letter(index: textN))
}
func printEXCL_Letter(index : Int) -> String {
let letters = ["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"]
var index = index;
index -= 1
let index_div = index / 26
if (index_div > 0){
return printEXCL_Letter(index: index_div) + letters[index % 26];
}
else {
return letters[index % 26]
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow