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

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

[{'answer_id': 22715, 'author': 'Joseph Sturtevant', 'author_id': 317, 'author_profile': 'https://Stackoverflow.com/users/317', 'pm_score': 6, 'selected': True, 'text': '<p>I once wrote this function to perform that exact task:</p>\n\n<pre><code>public static string Column(int column)\n{\n    column--;\n    if (column &gt;= 0 &amp;&amp; column &lt; 26)\n        return ((char)(\'A\' + column)).ToString();\n    else if (column &gt; 25)\n        return Column(column / 26) + Column(column % 26 + 1);\n    else\n        throw new Exception("Invalid Column #" + (column + 1).ToString());\n}\n</code></pre>\n'}, {'answer_id': 22719, 'author': 'goric', 'author_id': 940, 'author_profile': 'https://Stackoverflow.com/users/940', 'pm_score': -1, 'selected': False, 'text': '<p>I currently use this, but I have a feeling that it can be optimized.</p>\n\n<pre><code>private String GetNthExcelColName(int n)\n{\n    String firstLetter = "";  \n    //if number is under 26, it has a single letter name\n    // otherwise, it is \'A\' for 27-52, \'B\' for 53-78, etc\n    if(n &gt; 26)\n    {\n        //the Converts to double and back to int are just so Floor() can be used\n        Double value = Convert.ToDouble((n-1) / 26);\n        int firstLetterVal = Convert.ToInt32(Math.Floor(value))-1;\n        firstLetter = Convert.ToChar(firstLetterValue + 65).ToString();\n    }    \n\n    //second letter repeats\n    int secondLetterValue = (n-1) % 26;\n    String secondLetter = Convert.ToChar(secondLetterValue+65).ToString();\n\n    return firstLetter + secondLetter;\n}\n</code></pre>\n'}, {'answer_id': 22738, 'author': 'wcm', 'author_id': 2173, 'author_profile': 'https://Stackoverflow.com/users/2173', 'pm_score': 3, 'selected': False, 'text': "<p>Joseph's code is good but, if you don't want or need to use a VBA function, try this.</p>\n\n<p>Assuming that the value of n is in cell <code>A2</code>\nUse this function:</p>\n\n<pre><code>=MID(ADDRESS(1,A2),2,LEN(ADDRESS(1,A2))-3)\n</code></pre>\n"}, {'answer_id': 22766, 'author': 'PabloG', 'author_id': 394, 'author_profile': 'https://Stackoverflow.com/users/394', 'pm_score': 1, 'selected': False, 'text': '<p>I suppose you need VBA code:</p>\n\n<pre><code>Public Function GetColumnAddress(nCol As Integer) As String\n\nDim r As Range\n\nSet r = Range("A1").Columns(nCol)\nGetColumnAddress = r.Address\n\nEnd Function\n</code></pre>\n'}, {'answer_id': 22959, 'author': 'wcm', 'author_id': 2173, 'author_profile': 'https://Stackoverflow.com/users/2173', 'pm_score': 0, 'selected': False, 'text': "<p>All these code samples that these good people have posted look fine.  </p>\n\n<p>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.</p>\n\n<p>Shouldn't be that hard...</p>\n"}, {'answer_id': 23781, 'author': 'Jason Z', 'author_id': 2470, 'author_profile': 'https://Stackoverflow.com/users/2470', 'pm_score': 4, 'selected': False, 'text': '<p>A language agnostic algorithm would be as follows:</p>\n\n<pre><code>function getNthColumnName(int n) {\n   let curPower = 1\n   while curPower &lt; n {\n      set curPower = curPower * 26\n   }\n   let result = ""\n   while n &gt; 0 {\n      let temp = n / curPower\n      let result = result + char(temp)\n      set n = n - (curPower * temp)\n      set curPower = curPower / 26\n   }\n   return result\n</code></pre>\n\n<p>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</p>\n'}, {'answer_id': 37002, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 1, 'selected': False, 'text': '<p>This does what you want in VBA</p>\n\n<pre><code>Function GetNthExcelColName(n As Integer) As String\n    Dim s As String\n    s = Cells(1, n).Address\n    GetNthExcelColName = Mid(s, 2, InStr(2, s, "$") - 2)\nEnd Function\n</code></pre>\n'}, {'answer_id': 39952, 'author': 'Dick Kusleika', 'author_id': 4280, 'author_profile': 'https://Stackoverflow.com/users/4280', 'pm_score': 0, 'selected': False, 'text': '<p>Here\'s Gary Waters solution</p>\n\n<pre><code>Function ConvertNumberToColumnLetter2(ByVal colNum As Long) As String\n    Dim i As Long, x As Long\n    For i = 6 To 0 Step -1\n        x = (1 - 26 ^ (i + 1)) / (-25) - 1 ‘ Geometric Series formula\n        If colNum &gt; x Then\n            ConvertNumberToColumnLetter2 = ConvertNumberToColumnLetter2 &amp; Chr(((colNum - x - 1)\\ 26 ^ i) Mod 26 + 65)\n        End If\n    Next i\nEnd Function\n</code></pre>\n\n<p>via <a href="http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/" rel="nofollow noreferrer">http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/</a></p>\n'}, {'answer_id': 50472, 'author': 'Ralph M. Rickenbach', 'author_id': 4549416, 'author_profile': 'https://Stackoverflow.com/users/4549416', 'pm_score': 0, 'selected': False, 'text': "<p>Considering the comment of wcm (top value = xfd), you can calculate it like this;</p>\n\n<pre><code>function IntToExcel(n: Integer); string;\nbegin\n   Result := '';\n   for i := 2 down to 0 do \n   begin\n      if ((n div 26^i)) &gt; 0) or (i = 0) then\n         Result := Result + Char(Ord('A')+(n div (26^i)) - IIF(i&gt;0;1;0));\n      n := n mod (26^i);\n   end;\nend;\n</code></pre>\n\n<p>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).</p>\n"}, {'answer_id': 515791, 'author': 'Maslow', 'author_id': 57883, 'author_profile': 'https://Stackoverflow.com/users/57883', 'pm_score': 1, 'selected': False, 'text': '<p>This seems to work in vb.net</p>\n\n<pre><code>Public Function Column(ByVal pColumn As Integer) As String\n    pColumn -= 1\n    If pColumn &gt;= 0 AndAlso pColumn &lt; 26 Then\n        Return ChrW(Asc("A"c) + pColumn).ToString\n    ElseIf (pColumn &gt; 25) Then\n        Return Column(CInt(math.Floor(pColumn / 26))) + Column((pColumn Mod 26) + 1)\n    Else\n    stop\n        Throw New ArgumentException("Invalid column #" + (pColumn + 1).ToString)\n    End If\nEnd Function\n</code></pre>\n\n<p>I took Joseph\'s and tested it to BH, then fed it 980-1000 and it looked good.</p>\n'}, {'answer_id': 1258483, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': -1, 'selected': False, 'text': '<p>=CHAR(64+COLUMN())</p>\n'}, {'answer_id': 2292997, 'author': 'iDevlop', 'author_id': 78522, 'author_profile': 'https://Stackoverflow.com/users/78522', 'pm_score': 1, 'selected': False, 'text': '<p>In VBA, assuming lCol is the column number:   </p>\n\n<pre><code>function ColNum2Letter(lCol as long) as string\n    ColNum2Letter = Split(Cells(1, lCol).Address, "$")(0)\nend function\n</code></pre>\n'}, {'answer_id': 3550070, 'author': 'Matt Lewis', 'author_id': 428667, 'author_profile': 'https://Stackoverflow.com/users/428667', 'pm_score': 2, 'selected': False, 'text': "<pre><code>IF(COLUMN()&gt;=26,CHAR(ROUND(COLUMN()/26,1)+64)&amp;CHAR(MOD(COLUMN(),26)+64),CHAR(COLUMN()+64))\n</code></pre>\n\n<p>This works 2 letter columns (up until column <code>ZZ</code>).  You'd have to nest another if statement for 3 letter columns.  </p>\n\n<p>The formula above fails on columns <code>AY</code>, <code>AZ</code> and each of the following <code>nY</code> and <code>nZ</code> columns. The corrected formula is:</p>\n\n<pre><code>=IF(COLUMN()&gt;26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&amp;CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64)\n</code></pre>\n"}, {'answer_id': 4532562, 'author': 'Samuel Audet', 'author_id': 523744, 'author_profile': 'https://Stackoverflow.com/users/523744', 'pm_score': 5, 'selected': False, 'text': '<p>Here is the cleanest <em>correct</em> solution I could come up with (in Java, but feel free to use your favorite language):</p>\n\n<pre><code>String getNthColumnName(int n) {\n    String name = "";\n    while (n &gt; 0) {\n        n--;\n        name = (char)(\'A\' + n%26) + name;\n        n /= 26;\n    }\n    return name;\n}\n</code></pre>\n\n<p>But please do let me know of if you find a mistake in this code, thank you.</p>\n'}, {'answer_id': 4695873, 'author': 'Craig0409', 'author_id': 318875, 'author_profile': 'https://Stackoverflow.com/users/318875', 'pm_score': 3, 'selected': False, 'text': '<p>Thanks, Joseph Sturtevant!  Your code works perfectly - I needed it in vbscript, so figured I\'d share my version:</p>\n\n<pre><code>Function ColumnLetter(ByVal intColumnNumber)\n    Dim sResult\n    intColumnNumber = intColumnNumber - 1\n    If (intColumnNumber &gt;= 0 And intColumnNumber &lt; 26) Then\n        sResult = Chr(65 + intColumnNumber)\n    ElseIf (intColumnNumber &gt;= 26) Then\n        sResult = ColumnLetter(CLng(intColumnNumber \\ 26)) _\n                &amp; ColumnLetter(CLng(intColumnNumber Mod 26 + 1))\n    Else\n        err.Raise 8, "Column()", "Invalid Column #" &amp; CStr(intColumnNumber + 1)\n    End If\n    ColumnLetter = sResult\nEnd Function\n</code></pre>\n'}, {'answer_id': 10017783, 'author': 'James Evason', 'author_id': 900506, 'author_profile': 'https://Stackoverflow.com/users/900506', 'pm_score': 2, 'selected': False, 'text': '<p>And here is a conversion from the VBScript version to SQL Server 2000+.</p>\n\n<pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION [dbo].[GetExcelColRef] \n(\n    @col_seq_no int\n)\nRETURNS varchar(5)\nAS\nBEGIN\n\ndeclare @Result varchar(5)\nset @Result = \'\'\nset @col_seq_no = @col_seq_no - 1\nIf (@col_seq_no &gt;= 0 And @col_seq_no &lt; 26) \nBEGIN\n    set @Result = char(65 + @col_seq_no)\nEND\nELSE\nBEGIN\n    set @Result = [dbo].[GetExcelColRef] (@col_seq_no / 26) + \'\' + [dbo].[GetExcelColRef]  ((@col_seq_no % 26) + 1)\nEND\nReturn @Result\n\nEND\nGO\n</code></pre>\n'}, {'answer_id': 12614842, 'author': 'AndrewD', 'author_id': 20151, 'author_profile': 'https://Stackoverflow.com/users/20151', 'pm_score': 0, 'selected': False, 'text': "<p>FYI T-SQL to give the Excel column name given an ordinal (zero-based), as a single statement.</p>\n\n<p>Anything below 0 or above 16,383 (max columns in Excel2010) returns NULL.</p>\n\n<pre><code>; WITH TestData AS ( -- Major change points\n    SELECT -1 AS FieldOrdinal\n    UNION ALL\n    SELECT 0\n    UNION ALL\n    SELECT 25\n    UNION ALL\n    SELECT 26\n    UNION ALL\n    SELECT 701\n    UNION ALL\n    SELECT 702\n    UNION ALL\n    SELECT 703\n    UNION ALL\n    SELECT 16383\n    UNION ALL\n    SELECT 16384\n)\nSELECT\n      FieldOrdinal\n    , CASE\n       WHEN FieldOrdinal &lt; 0     THEN NULL\n       WHEN FieldOrdinal &lt; 26    THEN ''\n       WHEN FieldOrdinal &lt; 702   THEN CHAR (65 + FieldOrdinal / 26 - 1)\n       WHEN FieldOrdinal &lt; 16384 THEN CHAR (65 + FieldOrdinal / 676 - 1)\n                                    + CHAR (65 + (FieldOrdinal / 26) - (FieldOrdinal / 676) * 26 - 1)\n       ELSE NULL\n      END\n      + CHAR (65 + FieldOrdinal % 26)\n FROM TestData\n ORDER BY FieldOrdinal\n</code></pre>\n"}, {'answer_id': 13413291, 'author': 'PatrickDaemen', 'author_id': 1829086, 'author_profile': 'https://Stackoverflow.com/users/1829086', 'pm_score': 2, 'selected': False, 'text': "<p>FROM wcm: </p>\n\n<p>If you don't want to use VBA, you can use this\nreplace colnr with the number you want</p>\n\n<pre><code>=MID(ADDRESS(1,colnr),2,LEN(ADDRESS(1,colnr))-3)\n</code></pre>\n\n<p>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.\nNormally excel recalculates formula's only when their dependent references changes.</p>\n\n<p>It could be a performance killer, to use this formula.</p>\n"}, {'answer_id': 13882315, 'author': 'Jim Drannbauer', 'author_id': 1904556, 'author_profile': 'https://Stackoverflow.com/users/1904556', 'pm_score': 2, 'selected': False, 'text': '<p>Ruby one-liner:</p>\n\n<pre><code>def column_name_for(some_int)\n    some_int.to_s(26).split(\'\').map {|c| (c.to_i(26) + 64).chr }.join # 703 =&gt; "AAA"\nend\n</code></pre>\n\n<p>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.</p>\n\n<p>Fun.</p>\n'}, {'answer_id': 16959692, 'author': 'Kent Pawar', 'author_id': 985766, 'author_profile': 'https://Stackoverflow.com/users/985766', 'pm_score': 2, 'selected': False, 'text': '<p>This works fine in MS Excel 2003-2010. Should work for previous versions supporting the <em>Cells(...).<a href="http://msdn.microsoft.com/en-us/library/office/aa174749%28v=office.11%29.aspx" rel="nofollow">Address</a></em> function:</p>\n\n<ol>\n<li>For the 28th column - taking <code>columnNumber=28</code>; <code>Cells(1, columnNumber).Address</code> returns <code>"$AB$1"</code>.</li>\n<li>Doing a split on the <code>$</code> sign returns the array: <code>["","AB","1"]</code></li>\n<li>So <code>Split(Cells(1, columnNumber).Address, "$")(1)</code> gives you the column name <code>"AB"</code>.</li>\n</ol>\n\n<hr>\n\n<p><strong>UPDATE:</strong></p>\n\n<p>Taken from <a href="http://support.microsoft.com/kb/833402" rel="nofollow">How to convert Excel column numbers into alphabetical characters</a></p>\n\n<pre><code>\' The following VBA function is just one way to convert column number \n\' values into their equivalent alphabetical characters:\n\nFunction ConvertToLetter(iCol As Integer) As String\n   Dim iAlpha As Integer\n   Dim iRemainder As Integer\n   iAlpha = Int(iCol / 27)\n   iRemainder = iCol - (iAlpha * 26)\n   If iAlpha &gt; 0 Then\n      ConvertToLetter = Chr(iAlpha + 64)\n   End If\n   If iRemainder &gt; 0 Then\n      ConvertToLetter = ConvertToLetter &amp; Chr(iRemainder + 64)\n   End If\nEnd Function\n</code></pre>\n\n<p>APPLIES TO: Microsoft Office Excel 2007 SE / 2002 SE / 2000 SE / 97 SE</p>\n'}]
['https://Stackoverflow.com/questions/22708', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/940/']