42 thoughts on “Excel: Column Number to Letter(s)”

1. When I read this the first time I thought to myself “Hah, I’ll never need that. Damn that Craig, he’s stolen 60 seconds of my life.”

   Since my circumstances have changed and I needed to do exactly thins … some neurons fired, a quick search and *bamf*. I was here and my problem is solved.

   This internet thing rocks!!

   Thanks Craig.

2. Just in case someone wants this code in C#.
   It is a zero based column (i.e. Zero = “A”)

   public string ExcelColumnLetter(int intCol)
   {
   string strColumn;
   char letter1, letter2;
   int intFirstLetter = ((intCol) / 26);
   int intSecondLetter = (intCol % 26);
   intFirstLetter = intFirstLetter + 64;
   intSecondLetter = intSecondLetter + 65;

   if (intFirstLetter > 64)
   {
   letter1 = (char)intFirstLetter;
   }
   else
   letter1 = ‘ ‘;
   letter2 = (char)intSecondLetter;
   strColumn = string.Concat(letter1, letter2);
   return strColumn.Trim();
   }

3. Nice work, I needed the C# version of this, could have done it myself, but you’ve saved me the effort. Thanks!

   I trimmed it down a bit for my use. Maybe it’s a bit more efficient, who knows?

   private string excelColumnLetter(int intCol)
   {
   int intFirstLetter = ((intCol) / 26) + 64;
   int intSecondLetter = (intCol % 26) + 65;
   char letter1 = (intFirstLetter > 64) ? (char)intFirstLetter : ‘ ‘;
   return string.Concat(letter1, (char)intSecondLetter).Trim();
   }

4. I made a little change to account for the cases where there are three lettere

   public string ExcelColumnLetter(int intCol)
   {

   if(intCol > 16384){
   throw new Exception(“Index exceeds maximum columns allowed.”);
   }
   string strColumn;
   char letter1, letter2, FirstLetter;
   int InitialLetter = ((intCol) / 676);
   int intFirstLetter = ((intCol % 676) / 26);
   int intSecondLetter = (intCol % 26);
   InitialLetter = InitialLetter + 64;
   intFirstLetter = intFirstLetter + 65;
   intSecondLetter = intSecondLetter + 65;

   if (InitialLetter > 64)
   {
   FirstLetter = (char)InitialLetter;
   }
   else
   {
   FirstLetter = ‘ ‘;
   }

   if (intFirstLetter > 64)
   {
   letter1 = (char)intFirstLetter;
   }
   else
   {
   letter1 = ‘ ‘;
   }
   letter2 = (char)intSecondLetter;
   strColumn = FirstLetter + string.Concat(letter1, letter2);
   return strColumn.Trim();
   }

5. Real C# Conversion:

   public static string ColumnLetter(int colNum)
   {
   string s = “”;
   for (int i = Convert.ToInt32(Math.Log(Convert.ToDouble(25 * (Convert.ToDouble(colNum) + 1))) / Math.Log(26)) – 1; i >= 0; i–)
   {
   int x = Convert.ToInt32(Math.Pow(26, i + 1) – 1) / 25 – 1;
   if (colNum > x)
   {
   s += (char)(((colNum – x – 1) / Convert.ToInt32(Math.Pow(26, i))) % 26 + 65);
   }
   }
   return s;
   }

6. if you don’t want to use VB, the following will return the current column letter:

   IF(COLUMN()<=26,CHAR(96+COLUMN()),(CHAR(96+(ROUNDDOWN((COLUMN()-1)/26,0)))&CHAR(96+IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN(),26)))))

7. WOOHOO!!! Just what the doctor ordered. Ended up changing it a little but this was pretty much all done and ready to plug in. Thanks guys!

8. Just wanted to say thanks for posting this. It came in handy for me today.

9. please can anyone tell me what if i want the opposite of this function i want to convert the letters to numbers…how can i do that?

   thanks in advance
   any help is appreciated

10. dim strCol as string
    dim intCol as integer

    strCol = “K”
    intCol = Columns(strCol).Column

11. Yeah.. thanks a lot, i’ve used it and it rocks!

12. public string ExcelColumnLetter(int intCol)

    Above mentioned function is really good one.
    It is James Owen’s comment
    just it require “-1” for number of columns

    thx

13. ADAM! Thank you very much.

    In spanish and uppercase:

    =SI(COLUMNA()<=26;CARACTER(64+COLUMNA());(CARACTER(64+REDONDEAR.MENOS((COLUMNA()-1)/26;0)))&CARACTER(64+SI(RESIDUO(COLUMNA();26)=0;26;RESIDUO(COLUMNA();26))))

14. I modified some of the examplecode above, so this is what I ended up with.
    It works for columns with 3 letters and it’s written in vba.
    Column A = 1, B = 2 and so on.

    Function fnColumnNumberToLetter(ByVal lColNum As Long) As String

    If (lColNum > Columns.Count) Then
    MsgBox (“Index exceeds maximum columns allowed.”)
    Exit Function
    End If

    Dim strColumn As String
    Dim sLetter1 As String
    Dim sLetter2 As String
    Dim sFirstLetter As String

    Dim iInitialLetter As Integer
    Dim iFirstLetter As Integer
    Dim iSecondLetter As Integer

    ‘Three letters?
    If (lColNum > 702) Then
    iInitialLetter = Int((lColNum – 703) / 676)
    iInitialLetter = iInitialLetter + 65
    sFirstLetter = Chr(iInitialLetter)
    Else
    sFirstLetter = “”
    End If

    ‘Second letter?
    iFirstLetter = Int(((lColNum – 1 – 26) Mod 676) / 26)
    iFirstLetter = iFirstLetter + 65

    If (iFirstLetter > 64) Then
    sLetter1 = Chr(iFirstLetter)
    Else
    sLetter1 = “”
    End If

    ‘This letter always exists!
    iSecondLetter = (lColNum Mod 26)

    If (iSecondLetter = 0) Then
    iSecondLetter = 26
    End If
    iSecondLetter = iSecondLetter + 64
    sLetter2 = Chr(iSecondLetter)

    ‘Puts togehter the result…
    strColumn = Trim(sFirstLetter & sLetter1 & sLetter2)
    fnColumnNumberToLetter = strColumn

    End Function

15. umm… 3 lines of code for A – ZZZZZZZZZ…Z?

    public static string NumToLetter(int Col)
    {
    if (Col <= 26) return ((char)(Col + 64)).ToString();

    //puts us on Zero Bound Index… tis where my math is 1337.
    Col–;
    return NumToLetter(Col / 26) + NumToLetter((Col % 26) + 1);
    }

16. MattEvan16’s Code takes 15ms to generate NumToLetter references for 1 – 16384 (max excel 2007 columns)

    Not bad! And such a small amount of code too! Makes you wonder why everyone else was overthinking it

17. This is how a Beginner like me solved this issue (there are better ways to do it, f.e. using function)

    ‘capture ColumnID
    Dim ColumnID As String
    Dim TempColumnAddress As String
    Dim TempColumnID As String

    TempColumnAddress = ActiveCell.Address
    TempColumnID = TempColumnAddress

    TempColumnID = Left(TempColumnID, 3) ‘$AA or $A$
    TempColumnID = Right(TempColumnID, 1) ‘Letter or $
    If TempColumnID = “$” Then
    ColumnID = Left(TempColumnAddress, 2)
    ColumnID = Right(ColumnID, 1)
    Else
    ColumnID = Left(TempColumnAddress, 3)
    ColumnID = Right(ColumnID, 2)
    End If

18. I have a spreadsheet where i need to count the number of instances of a certain text within a row however i only have the column numbers not column letters like a countif function.
    Can anyone help?

19. how to convert over 2147483647

20. This is not as concise as MattEvans16’s C code, but for those who need it in VBA, here is a recursive function that works for any number of letters in the column address…

    Function ColNumToAlpha(ColumnNumber As Integer) As String
    ColNum = ColumnNumber
    ColLetters = “”
    Do
    ColLetters = Chr(((ColNum – 1) Mod 26) + 65) & ColLetters
    ColNum = Int((ColNum – ((ColNum – 1) Mod 26)) / 26)
    Loop While ColNum > 0
    ColumnNumToAlpha = ColLetters
    End Function

21. Ooops – typo in the function name on the return. Also declaring the variables in this version…

    Function ColNumToAlpha(ColumnNumber As Integer) As String
    Dim ColNum as Integer
    Dim ColLetters as String

    ColNum = ColumnNumber
    ColLetters = “”

    Do
    ColLetters = Chr(((ColNum – 1) Mod 26) + 65) & ColLetters
    ColNum = Int((ColNum – ((ColNum – 1) Mod 26)) / 26)
    Loop While ColNum > 0

    ColNumToAlpha = ColLetters

    End Function

22. Here is a quick test program for the VB code. Just comment in/out the different test cases:

    Sub TestColNumToLetter()
    Dim TestNum As Integer

    ‘ TestNum = 25 ‘Y
    ‘ TestNum = 270 ‘JJ
    ‘ TestNum = 703 ‘AAA
    TestNum = 16343 ‘XDO

    MsgBox “Column Address in Alpha: ” & ColNumToAlpha(TestNum), vbOKOnly, “Column Number: ” & TestNum

    End Sub

    @INCOGNITO: If you want to convert numbers beyond the integer range, just convert the variable type to double. I’m not sure why you would want to do that since even Excel 2007 is limited to 16,384 columns (XFD), but I tested it with my VB code and 2147483647 returned “FXSHRXX”. I’ll let you do the math to verify that’s correct.

23. Here is the quick way:

    Split(Columns(intColumnNumber).Address, “:”)(0)

24. Pour ceux qui en auraient besoin en Pascal (Delphi):

    function ColNumToAlpha(ColumnNumber: integer): string;
    var
    ColNum : integer;
    ColLetters : string;
    begin
    ColNum := ColumnNumber;
    ColLetters := ”;

    while (ColNum > 0) do begin
    ColLetters := Chr(((ColNum – 1) mod 26) + 65) + ColLetters;
    ColNum := (ColNum – ((ColNum – 1) mod 26)) div 26;
    end;

    Result := ColLetters;
    end;

25. /* PHP VERSION COLUMN NUMBER TO EXCEL COLUMN NAME */
    /* THANKS TO MattEvans16 he did the algorithm above*/
    /* Useful for php / mysql excel export using phpexcel from codeplex */
    function NumToLetter($Col)
    {
    if ($Col <= 26) return chr($Col + 64);

    //puts us on Zero Bound Index… tis where my math is 1337.
    $Col–;
    return NumToLetter($Col / 26) . NumToLetter(($Col % 26) + 1);
    }

26. //cold fusion version
    function NumToLetter(Col){
    Col = int(Col);
    if (Col <= 26) return ToString(Chr(Col + 64));

    //puts us on Zero Bound Index… tis where my math is 1337.
    Col = (Col – 1);
    return NumToLetter(Col / 26) + NumToLetter((Col MOD 26) + 1);
    }

27. MattEvans16:

    “puts us on Zero Bound Index… tis where my math is 1337.”

    What does that even mean?

    And why does everyone keep repeating that?

28. @Brad

    zero bound index … instead of starting a counter at 1 and incrementing to to whatever, he starts his counter from 0 (in lots of computer stuff starting from 0 is very useful).

    1337 … upsidedown, backwards, and read as letters, it’s LEET which, people who speak 1337, know means “elite”.

    Why does everyone repeat that … because they are paying homage to MattEvans16’s total awesomeness.

29. My direct translation of MattEvans16’s (your math is indeed 133t) C code into VBA:

    Public Function NumToLetter(iCol As Integer) As String
    If (iCol <= 26) Then
    NumToLetter = Chr(iCol + 64)
    Else
    iCol = iCol – 1
    NumToLetter = NumToLetter(iCol / 26) + NumToLetter((iCol Mod 26) + 1)
    End If
    End Function

30. Works perfect!! Thanks

31. To Marcus Hansson,

    Thanks man. That is exactly what I needed.

32. public int getPower(int num, int power) {
    if (num == 0) return 1;
    if (getInterval(power) > num) return power;
    else return getPower(num, power + 1);
    }

    public int getInterval(int power) {
    return (int) (Math.pow(26, power + 1) – 26) / 25;
    }

    public char get2Ascii(int intColumn) {
    int intAscii = 65;
    intAscii += intColumn;
    return (char) intAscii;
    }

    public String get2Alphabet(int number) {
    String alphabet = “”;
    int asciiCode = 0;
    int power = getPower(number, 0);
    int prePower = power – 1;
    int preInterval = getInterval(prePower);
    int interval = number – preInterval;
    while (power > 0) {
    asciiCode = (int) (interval / Math.pow(26, power – 1));
    alphabet = alphabet + get2Ascii(asciiCode);
    interval = interval – (int) Math.pow(26, power – 1) * asciiCode;
    power–;
    }
    return alphabet;
    }

33. Yikes! A lot of calculations there. I wouldn’t expect that to be very performant. I’ve written code both to convert from an integer to a spreadsheet column label, and to convert the column label back to an integer. I’ve posted it at http://www.blackbeltcoder.com/Articles/strings/converting-between-integers-and-spreadsheet-column-labels.

34. Here is another short recursive version

    Public Function GetHeaders(N As Integer) As String
    Dim theNewVal As Integer, theCurLetter As String

    theNewVal = (N – 1) \ 26
    thelowletterval = (N – 1) Mod 26
    theCurLetter = Chr(thelowletterval + 65)

    If theNewVal > 0 Then
    theCurLetter = GetHeaders(theNewVal) & theCurLetter
    End If

    GetHeaders = theCurLetter

    End Function

35. Adam, thanks for the formula! (Gabriel, you may be able to use this, too). i prefer that to VBA code most of the time. Anyway, one simplification in the last part. There is no need for an “IF” there: IF(COLUMN()<=26,CHAR(96+COLUMN()),(CHAR(96+(ROUNDDOWN((COLUMN()-1)/26,0)))&CHAR(97+MOD(COLUMN()-1,26))))).

36. With due credit to the original solution, here is an even simpler one, about a 30% reduction in length:

    IF(COLUMN()<=26,CHAR(96+COLUMN()),CHAR(96+INT((COLUMN()-1)/26))&CHAR(97+MOD(COLUMN()-1,26)))

37. Even easier

    Dim myColumn As String
    myColumn = Replace(Cells(1, maxcol).Address(0, 0), “1”, “”)

38. Pingback: Excel column1 | Kedewang

39. Hi All
    At MSN site it gives vb code (for Excel Macros) that only works to around 450 columns, after that it starts to become in accurate due to division. I tried these above but can’t convert accurately to run in a Macro in Excel – can someone enlighten me please! For example I have columns past VQ (no. 589).

40. =LEFT(SUBSTITUTE(CELL(“address”,OFFSET(A1,0,A1-1)),”$”,””),FIND(“$”,CELL(“address”,OFFSET(A1,0,A1-1)),2)-2)

41. I would LOVE to do the opposite. IE

    static int GetColumIndexFromHeader(string header)
    {

    }

    so that if I call
    GetColumIndexFromHeader(“A”) = 0
    GetColumIndexFromHeader(“Z”) = 25

    GetColumIndexFromHeader(“ABA”) = ???

    PLEASE HELP

42. I translated MattEvans16′s into VB (.Net in my case) and there is one difference that I want to emphasize: need to use ‘\’ NOT ‘/’. ‘\’ is integer devision and is need for this code to work ALL the time.

    I used JJ’s test values and 16343 came out XEO.
    These are my test values (all of the once above 1000 will fail without ‘\’:
    retVal = NumToLetter(1) ‘A
    Debug.Print(retVal)
    retVal = NumToLetter(25) ‘Y
    Debug.Print(retVal)
    retVal = NumToLetter(270) ‘JJ
    Debug.Print(retVal)
    retVal = NumToLetter(703) ‘AAA
    Debug.Print(retVal)
    retVal = NumToLetter(1007) ‘ALS
    Debug.Print(retVal)
    retVal = NumToLetter(6774) ‘IZN
    Debug.Print(retVal)
    retVal = NumToLetter(16343) ‘XDO

    Once again thanks MattEvans16 – I love recursion, especially when it works well.

    This is my VB.Net code:

    Public Function NumToLetter(ByVal colNumber As Long) As String

    If colNumber <= 26 Then
    'Debug.Print(Chr(colNumber + 64))
    Return Chr(colNumber + 64)
    End If

    colNumber = colNumber – 1
    NumToLetter = NumToLetter(colNumber \ 26) + NumToLetter((colNumber Mod 26) + 1)

    End Function

Comments are closed.