How would you determine the column name (e.g. "AQ" or "BH") of the nth column in Excel?

Edit: A language-agnostic algorithm to determine this is the main goal here.

How would you determine the column name (e.g. "AQ" or "BH") of the nth column in Excel?

Edit: A language-agnostic algorithm to determine this is the main goal here.

+5
A:

I once wrote this function to perform that exact task:

```
public static string Column(int column)
{
column--;
if (column >= 0 && column < 26)
return ((char)('A' + column)).ToString();
else if (column > 25)
return Column(column / 26) + Column(column % 26 + 1);
else
throw new Exception("Invalid Column #" + (column + 1).ToString());
}
```

Joseph Sturtevant
2008-08-22 15:53:47

Onorio Catenacci
2008-09-08 19:30:45

@Onorio Catenacci: "column > 25" is another way of saying "column >= 26". I find the latter clearer, but both are correct.

technomalogical
2009-05-07 18:53:56
that is sooooo long !

iDevlop
2010-02-18 23:26:17
A:

I currently use this, but I have a feeling that it can be optimized.

```
private String GetNthExcelColName(int n)
{
String firstLetter = "";
//if number is under 26, it has a single letter name
// otherwise, it is 'A' for 27-52, 'B' for 53-78, etc
if(n > 26)
{
//the Converts to double and back to int are just so Floor() can be used
Double value = Convert.ToDouble((n-1) / 26);
int firstLetterVal = Convert.ToInt32(Math.Floor(value))-1;
firstLetter = Convert.ToChar(firstLetterValue + 65).ToString();
}
//second letter repeats
int secondLetterValue = (n-1) % 26;
String secondLetter = Convert.ToChar(secondLetterValue+65).ToString();
return firstLetter + secondLetter;
}
```

theprise
2008-08-22 15:58:03

+1
A:

Joseph's code is good but, if you don't want or need to use a VBA function, try this.

Assuming that the value of n is in cell A2 Use this function:

- =MID(ADDRESS(1,A2),2,LEN(ADDRESS(1,A2))-3)

wcm
2008-08-22 16:03:49

A:

I suppose you need VBA code:

```
Public Function GetColumnAddress(nCol As Integer) As String
Dim r As Range
Set r = Range("A1").Columns(nCol)
GetColumnAddress = r.Address
End Function
```

PabloG
2008-08-22 16:11:02

A:

All these code samples that these good people have posted look fine.

There is one thing to be aware of. Starting with Office 2007, Excel actually has up to 16,384 columns. That translates to XFD (the old max of 256 colums was IV). You will have to modify these methods somewhat to make them work for three characters.

Shouldn't be that hard...

wcm
2008-08-22 17:30:09

+4
A:

A language agnostic algorithm would be as follows:

```
function getNthColumnName(int n) {
let curPower = 1
while curPower < n {
set curPower = curPower * 26
}
let result = ""
while n > 0 {
let temp = n / curPower
let result = result + char(temp)
set n = n - (curPower * temp)
set curPower = curPower / 26
}
return result
```

This algorithm also takes into account if Excel gets upgraded again to handle more than 16k columns. If you really wanted to go overboard, you could pass in an additional value and replace the instances of 26 with another number to accomodate alternate alphabets

Jason Z
2008-08-23 00:15:11

A:

This does what you want in VBA

```
Function GetNthExcelColName(n As Integer) As String
Dim s As String
s = Cells(1, n).Address
GetNthExcelColName = Mid(s, 2, InStr(2, s, "$") - 2)
End Function
```

vzczc
2008-08-31 18:12:13

A:

Here's Gary Waters solution

```
Function ConvertNumberToColumnLetter2(ByVal colNum As Long) As String
Dim i As Long, x As Long
For i = 6 To 0 Step -1
x = (1 - 26 ^ (i + 1)) / (-25) - 1 ‘ Geometric Series formula
If colNum > x Then
ConvertNumberToColumnLetter2 = ConvertNumberToColumnLetter2 & Chr(((colNum - x - 1)\ 26 ^ i) Mod 26 + 65)
End If
Next i
End Function
```

via http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/

Dick Kusleika
2008-09-02 16:26:56

A:

Considering the comment of wcm (top value = xfd), you can calculate it like this;

```
function IntToExcel(n: Integer); string;
begin
Result := '';
for i := 2 down to 0 do
begin
if ((n div 26^i)) > 0) or (i = 0) then
Result := Result + Char(Ord('A')+(n div (26^i)) - IIF(i>0;1;0));
n := n mod (26^i);
end;
end;
```

There are 26 characters in the alphabet and we have a number system just like hex or binary, just with an unusual character set (A..Z), representing positionally the powers of 26: (26^2)(26^1)(26^0).

Ralph Rickenbach
2008-09-08 19:23:14

A:

This seems to work in vb.net

```
Public Function Column(ByVal pColumn As Integer) As String
pColumn -= 1
If pColumn >= 0 AndAlso pColumn < 26 Then
Return ChrW(Asc("A"c) + pColumn).ToString
ElseIf (pColumn > 25) Then
Return Column(CInt(math.Floor(pColumn / 26))) + Column((pColumn Mod 26) + 1)
Else
stop
Throw New ArgumentException("Invalid column #" + (pColumn + 1).ToString)
End If
End Function
```

I took Joseph's and tested it to BH, then fed it 980-1000 and it looked good.

Maslow
2009-02-05 12:57:50

A:

Assuming lngCol is the column number:

```
function ColNum2Letter(lngCol as long) as string
dim arAdr
arAdr = split(cells(1,lngCol).address,"$")
ColNum2Letter= arAdr(ubound(arAdr))
end function
```

iDevlop
2010-02-18 23:25:14

A:

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

This works 2 letter columns (up until column ZZ). You'd have to nest another if statement for 3 letter columns.

Matt Lewis
2010-08-23 17:27:21