How do I find the Excel column name that corresponds to a given integer? [duplicate]

31
votes

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.

20
algorithmexcellanguage-agnostic
Do not delete this. A) It is needed as stub to increase chances of searches finding the canonical. B) It has good answers. – Lance Roberts
The general problem being solved here is conversion to/from bijective numeration, specifically bijective base-26. – ecatmur

20 Answers

41
votes

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());
}
26
votes

Here is the cleanest correct solution I could come up with (in Java, but feel free to use your favorite language):

String getNthColumnName(int n) {
    String name = "";
    while (n > 0) {
        n--;
        name = (char)('A' + n%26) + name;
        n /= 26;
    }
    return name;
}

But please do let me know of if you find a mistake in this code, thank you.

11
votes

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

7
votes

Thanks, Joseph Sturtevant! Your code works perfectly - I needed it in vbscript, so figured I'd share my version:

Function ColumnLetter(ByVal intColumnNumber)
    Dim sResult
    intColumnNumber = intColumnNumber - 1
    If (intColumnNumber >= 0 And intColumnNumber < 26) Then
        sResult = Chr(65 + intColumnNumber)
    ElseIf (intColumnNumber >= 26) Then
        sResult = ColumnLetter(CLng(intColumnNumber \ 26)) _
                & ColumnLetter(CLng(intColumnNumber Mod 26 + 1))
    Else
        err.Raise 8, "Column()", "Invalid Column #" & CStr(intColumnNumber + 1)
    End If
    ColumnLetter = sResult
End Function
6
votes

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)
3
votes
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.

The formula above fails on columns AY, AZ and each of the following nY and nZ columns. The corrected formula is:

=IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64)
3
votes

Ruby one-liner:

def column_name_for(some_int)
    some_int.to_s(26).split('').map {|c| (c.to_i(26) + 64).chr }.join # 703 => "AAA"
end

It converts the integer to base26 then splits it and does some math to convert each character from ascii. Finally joins 'em all back together. No division, modulus, or recursion.

Fun.

3
votes

FROM wcm:

If you don't want to use VBA, you can use this replace colnr with the number you want

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

Please be aware of the fact that this formula is volatile because of the usage of the ADDRESS function. Volatile functions are functions that are recalculated by excel after EVERY change. Normally excel recalculates formula's only when their dependent references changes.

It could be a performance killer, to use this formula.

2
votes

And here is a conversion from the VBScript version to SQL Server 2000+.

CREATE FUNCTION [dbo].[GetExcelColRef] 
(
    @col_seq_no int
)
RETURNS varchar(5)
AS
BEGIN

declare @Result varchar(5)
set @Result = ''
set @col_seq_no = @col_seq_no - 1
If (@col_seq_no >= 0 And @col_seq_no < 26) 
BEGIN
    set @Result = char(65 + @col_seq_no)
END
ELSE
BEGIN
    set @Result = [dbo].[GetExcelColRef] (@col_seq_no / 26) + '' + [dbo].[GetExcelColRef]  ((@col_seq_no % 26) + 1)
END
Return @Result

END
GO
2
votes

This works fine in MS Excel 2003-2010. Should work for previous versions supporting the Cells(...).Address function:

  1. For the 28th column - taking columnNumber=28; Cells(1, columnNumber).Address returns "$AB$1".
  2. Doing a split on the $ sign returns the array: ["","AB","1"]
  3. So Split(Cells(1, columnNumber).Address, "$")(1) gives you the column name "AB".

UPDATE:

Taken from How to convert Excel column numbers into alphabetical characters

' The following VBA function is just one way to convert column number 
' values into their equivalent alphabetical characters:

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

APPLIES TO: Microsoft Office Excel 2007 SE / 2002 SE / 2000 SE / 97 SE

1
votes

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
1
votes

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
1
votes

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.

1
votes

In VBA, assuming lCol is the column number: 

function ColNum2Letter(lCol as long) as string
    ColNum2Letter = Split(Cells(1, lCol).Address, "$")(0)
end function
0
votes

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

0
votes

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/

0
votes

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

0
votes

FYI T-SQL to give the Excel column name given an ordinal (zero-based), as a single statement.

Anything below 0 or above 16,383 (max columns in Excel2010) returns NULL.

; WITH TestData AS ( -- Major change points
    SELECT -1 AS FieldOrdinal
    UNION ALL
    SELECT 0
    UNION ALL
    SELECT 25
    UNION ALL
    SELECT 26
    UNION ALL
    SELECT 701
    UNION ALL
    SELECT 702
    UNION ALL
    SELECT 703
    UNION ALL
    SELECT 16383
    UNION ALL
    SELECT 16384
)
SELECT
      FieldOrdinal
    , CASE
       WHEN FieldOrdinal < 0     THEN NULL
       WHEN FieldOrdinal < 26    THEN ''
       WHEN FieldOrdinal < 702   THEN CHAR (65 + FieldOrdinal / 26 - 1)
       WHEN FieldOrdinal < 16384 THEN CHAR (65 + FieldOrdinal / 676 - 1)
                                    + CHAR (65 + (FieldOrdinal / 26) - (FieldOrdinal / 676) * 26 - 1)
       ELSE NULL
      END
      + CHAR (65 + FieldOrdinal % 26)
 FROM TestData
 ORDER BY FieldOrdinal
-1
votes

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

=CHAR(64+COLUMN())