How to convert a column number e g 127 into an Excel column e g AA

Question

How do you convert a numerical number to an Excel column name in C  without using automation getting the value directly from Excel   Excel 2007 has a possible range of 1 to 16384  which is the number of columns that it supports  The resulting values should be in the form of excel column names  e g  A  AA  AAA etc

User · Answer

I m using this one in VB NET 2003 and it works well     Private Function GetExcelColumnName ByVal aiColNumber As Integer  As String     Dim BaseValue As Integer   Convert ToInt32   A   Chars 0   - 1     Dim lsReturn As String   String Empty      If  aiColNumber  gt  26  Then         lsReturn   GetExcelColumnName Convert ToInt32  Format aiColNumber   26   0 0   Split       0        End If      GetExcelColumnName   lsReturn   Convert ToChar BaseValue    aiColNumber Mod 26   End Function

User · Answer

JavaScript Solution         Calculate the column letter abbreviation from a 1 based index     param  Number  value     returns  string      getColumnFromIndex   function  value        var base    ABCDEFGHIJKLMNOPQRSTUVWXYZ  split          var remainder  result           do           remainder   value   26          result   base  remainder    26  - 1    result          value   Math floor value   26         while  value  gt  0       return result

User · Answer

This is a common question asked in coding test  it has some constraints  max columns per row  702 output should have row number column name e g  for 703 answer is 2A   note  i have just modified existing code from another answer  here is the code for the same       static string GetExcelColumnName long columnNumber                  max number of column per row         const long maxColPerRow   702            find row number         long rowNum    columnNumber   maxColPerRow             find tierable columns in the row          long dividend   columnNumber -  maxColPerRow   rowNum            string columnName   String Empty           long modulo           while  dividend  gt  0                        modulo    dividend - 1    26              columnName   Convert ToChar 65   modulo  ToString     columnName              dividend    int   dividend - modulo    26                      return rowNum 1  columnName

User · Answer

NodeJS implementation         getColumnFromIndex   Helper that returns a column value  A-XFD  for an index value  integer     The column follows the Common Spreadsheet Format e g   A  AA  AAA    See https   stackoverflow com questions 181596 how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa 3444285 3444285    param numVal  Integer    return String    getColumnFromIndex  function numVal      var dividend   parseInt numVal      var columnName          var modulo     while  dividend  gt  0          modulo    dividend - 1    26        columnName   String fromCharCode 65   modulo    columnName        dividend   parseInt  dividend - modulo    26           return columnName       Thanks to Convert excel column alphabet  e g  AA  to number  e g   25   And in reverse         getIndexFromColumn   Helper that returns an index value  integer  for a column value  A-XFD     The column follows the Common Spreadsheet Format e g   A  AA  AAA    See https   stackoverflow com questions 9905533 convert-excel-column-alphabet-e-g-aa-to-number-e-g-25    param strVal  String    return Integer    getIndexFromColumn  function val      var base    ABCDEFGHIJKLMNOPQRSTUVWXYZ   i  j  result   0     for  i   0  j   val length - 1  i  lt  val length  i    1  j -  1          result    Math pow base length  j     base indexOf val i     1           return result

User · Answer

If you are wanting to reference the cell progmatically then you will get much more readable code if you use the Cells method of a sheet   It takes a row and column index instead of a traditonal cell reference   It is very similar to the Offset method

User · Answer

Here is a simpler solution for zero based column Index  public static string GetColumnIndexNumberToExcelColumn int columnIndex                        int offset   columnIndex   26              int multiple   columnIndex   26               int initialSeed   65   Represents column  quot A quot              if  multiple    0                                return Convert ToChar initialSeed   offset  ToString                               return   quot  Convert ToChar initialSeed   multiple - 1   Convert ToChar initialSeed   offset   quot

User · Answer

Easy with recursion   public static string GetStandardExcelColumnName int columnNumberOneBased      int baseValue   Convert ToInt32  A      int columnNumberZeroBased   columnNumberOneBased - 1     string ret          if  columnNumberOneBased  gt  26          ret   GetStandardExcelColumnName columnNumberZeroBased   26           return ret   Convert ToChar baseValue    columnNumberZeroBased   26

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def ColIdxToXlName idx       if idx  lt  1          raise ValueError  Index is too small       result          while True          if idx  gt  26              idx  r   divmod idx - 1  26              result   chr r   ord  A      result         else              return chr idx   ord  A   - 1    result   for i in xrange 1  1024       print   4d    s     i  ColIdxToXlName i

User · Answer

Saw one other VBA answer - this can be done in excel-vba with a 1 line UDF    Function GetColLetter ByVal colID As Integer  As String     GetColLetter   Split Cells 1  colID  Address       1  End Function

User · Answer

For what it is worth  here is Graham s code in Powershell   function ConvertTo-ExcelColumnID   param        parameter Position   0          HelpMessage    A 1-based index to convert to an excel column ID  e g  2   gt   B   29   gt   AC            Mandatory    true        int  index      string  result       if   index -le 0         return  result     while   index -gt 0         int  modulo     index - 1    26       character    char   modulo    int  char  A         result    character    result       int  index     index -  modulo    26     return  result

User · Answer

I discovered an error in my first post  so I decided to sit down and do the the math   What I found is that the number system used to identify Excel columns is not a base 26 system  as another person posted   Consider the following in base 10   You can also do this with the letters of the alphabet   Space                          S1  S2  S3   S1  S2  S3                                     0  00  000     A  AA  AAA                                     1  01  001     B  AB  AAB                                                                                                          9  99  999     Z  ZZ  ZZZ Total states in space     10  100  1000   26  676  17576 Total States                1110                18278  Excel numbers columns in the individual alphabetical spaces using base 26   You can see that in general  the state space progression is a  a 2  a 3      for some base a  and the total number of states is a   a 2   a 3           Suppose you want to find the total number of states A in the first N spaces   The formula for doing so is A    a  a N - 1    a-1    This is important because we need to find the space N that corresponds to our index K   If I want to find out where K lies in the number system I need to replace A with K and solve for N   The solution is N   log base a   A  a-1  a  1    If I use the example of a   10 and K   192  I know that N   2 23804       This tells me that K lies at the beginning of the third space since it is a little greater than two   The next step is to find exactly how far in the current space we are   To find this  subtract from K the A generated using the floor of N   In this example  the floor of N is two   So  A    10  10 2     1   10-1    110  as is expected when you combine the states of the first two spaces   This needs to be subtracted from K because these first 110 states would have already been accounted for in the first two spaces   This leaves us with 82 states   So  in this number system  the representation of 192 in base 10 is 082     The C  code using a base index of zero is      private string ExcelColumnIndexToName int Index                string range   string Empty          if  Index  lt  0   return range          int a   26          int x    int Math Floor Math Log  Index     a - 1    a   1  a            Index -   int  Math Pow a  x  - 1    a    a - 1           for  int i   x 1  Index   i  gt  0  i--                        range     char  65   Index   a   ToString     range              Index    a                    return range            Old Post  A zero-based solution in C        private string ExcelColumnIndexToName int Index                string range               if  Index  lt  0   return range          for int i 1 Index   i  gt  0 i 0                        range     char  65   Index   26   ToString     range              Index    26                    if  range Length  gt  1  range     char   int range 0  - 1   ToString     range Substring 1           return range

User · Answer

Another VBA way  Public Function GetColumnName TargetCell As Range  As String     GetColumnName   Split CStr TargetCell Cells 1  1  Address        1  End Function

User · Answer

In Delphi  Pascal    function GetExcelColumnName columnNumber  integer   string  var   dividend  modulo  integer  begin   Result          dividend    columnNumber    while dividend  gt  0 do begin     modulo     dividend - 1  mod 26      Result    Chr 65   modulo    Result      dividend     dividend - modulo  div 26    end  end

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def excel column number to name column number       output          index   column number-1     while index  gt   0          character   chr  index 26  ord  A            output   output   character         index   index 26 - 1      return output   -1    for i in xrange 1  1024       print   4d    s     i  excel column number to name i     Passed these test cases    Column Number  494286     ABCDZ  Column Number  27    AA Column Number  52    AZ

User · Answer

private String getColumn int c        String s           do           s    char   A     c   26     s          c    26        while  c--  gt  0       return s      Its not exactly base 26  there is no 0 in the system  If there was   Z  would be followed by  BA  not by  AA

User · Answer

Easy with recursion   public static string GetStandardExcelColumnName int columnNumberOneBased      int baseValue   Convert ToInt32  A      int columnNumberZeroBased   columnNumberOneBased - 1     string ret          if  columnNumberOneBased  gt  26          ret   GetStandardExcelColumnName columnNumberZeroBased   26           return ret   Convert ToChar baseValue    columnNumberZeroBased   26

User · Answer

Refining the original solution  in C     public static class ExcelHelper       private static Dictionary lt UInt16  String gt  l DictionaryOfColumns       public static ExcelHelper             l DictionaryOfColumns   new Dictionary lt ushort  string gt  256              public static String GetExcelColumnName UInt16 l Column                UInt16 l ColumnCopy   l Column          String l Chars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ           String l rVal               UInt16 l Char            if  l DictionaryOfColumns ContainsKey l Column     true                        l rVal   l DictionaryOfColumns l Column                     else                       while  l ColumnCopy  gt  26                                l Char   l ColumnCopy   26                  if  l Char    0                      l Char   26                   l ColumnCopy    l ColumnCopy - l Char    26                  l rVal   l Chars l Char    l rVal                            if  l ColumnCopy    0                  l rVal   l Chars l ColumnCopy    l rVal               l DictionaryOfColumns ContainsKey l Column    l rVal                     return l rVal

User · Answer

This is the question all others as well as Google redirect to so I m posting this here   Many of these answers are correct but too cumbersome for simple situations such as when you don t have over 26 columns  If you have any doubt whether you might go into double character columns then ignore this answer  but if you re sure you won t  then you could do it as simple as this in C    public static char ColIndexToLetter short index        if  index  lt  0    index  gt  25  throw new ArgumentException  Index must be between 0 and 25         return  char   A    index       Heck  if you re confident about what you re passing in you could even remove the validation and use this inline    char   A    index    This will be very similar in many languages so you can adapt it as needed   Again  only use this if you re 100  sure you won t have more than 26 columns

User · Answer

If you are wanting to reference the cell progmatically then you will get much more readable code if you use the Cells method of a sheet   It takes a row and column index instead of a traditonal cell reference   It is very similar to the Offset method

User · Answer

You might need conversion both ways  e g from Excel column adress like AAZ to integer and from any integer to Excel   The two methods below will do just that   Assumes 1 based indexing  first element in your  arrays  are element number 1  No limits on size here  so you can use adresses like ERROR and that would be column number 2613824      public static string ColumnAdress int col      if  col  lt   26         return Convert ToChar col   64  ToString          int div   col   26    int mod   col   26    if  mod    0   mod   26 div--     return ColumnAdress div    ColumnAdress mod      public static int ColumnNumber string colAdress      int   digits   new int colAdress Length     for  int i   0  i  lt  colAdress Length    i          digits i    Convert ToInt32 colAdress i   - 64        int mul 1 int res 0    for  int pos   digits Length - 1  pos  gt   0  --pos          res    digits pos    mul      mul    26        return res

User · Answer

F  version of each way  let rec getExcelColumnName x    if x lt 26 then int  A  x  gt char  gt string else  x 26-1  gt c   c x 26    pardon the minimizing  was working on a better version of https   stackoverflow com a 4500043 57883   and the opposite direction      return values start at 0 let getIndexFromExcelColumnName  x string        let a   int  A      let fPow len i           Math Pow 26   len - 1 - i   gt  float            gt  int      let getValue len i c            int c - a   1   fPow len i     let f i   getValue x Length i x  i       0    x Length - 1        gt  Seq map f       gt  Seq sum       gt  fun x - gt  x - 1

User · Answer

Here is an Actionscript version   private var columnNumbers Array     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         private function getExcelColumnName columnNumber int    String          var dividend int   columnNumber          var columnName String               var modulo int           while  dividend  gt  0                        modulo    dividend - 1    26              columnName   columnNumbers modulo    columnName              dividend   int  dividend - modulo    26                       return columnName

User · Answer

Here s how I do it   private string GetExcelColumnName int columnNumber        int dividend   columnNumber      string columnName   String Empty      int modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   Convert ToChar 65   modulo  ToString     columnName          dividend    int   dividend - modulo    26               return columnName

User · Answer

I wanted to throw in my static class I use  for interoping between col index and col Label  I use a modified accepted answer for my ColumnLabel Method  public static class Extensions       public static string ColumnLabel this int col                var dividend   col          var columnLabel   string Empty          int modulo           while  dividend  gt  0                        modulo    dividend - 1    26              columnLabel   Convert ToChar 65   modulo  ToString     columnLabel              dividend    int   dividend - modulo    26                       return columnLabel            public static int ColumnIndex this string colLabel                    AD   1   26 1     4   26 0              var colIndex   0          for int ind   0  pow   colLabel Count  -1  ind  lt  colLabel Count      ind  --pow                        var cVal   Convert ToInt32 colLabel ind   - 64    col A is index 1             colIndex    cVal     int Math Pow 26  pow                      return colIndex            Use this like     30 ColumnLabel        AD   AD  ColumnIndex       30

User · Answer

This answer is in javaScript   function getCharFromNumber columnNumber       var dividend   columnNumber      var columnName           var modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo  toString     columnName          dividend   parseInt  dividend - modulo    26              return  columnName

User · Answer

Coincise and elegant Ruby version   def col name col idx      name          while col idx gt 0         mod        col idx-1  26         name       65 mod  chr   name         col idx     col idx-mod  26  to i     end     name end

User · Answer

I realise the question relates to C  however  if anyone reading needs to do the same thing with Java then the following may be useful   It turns out that this can easily be done using the the  CellReference  class in Jakarta POI  Also  the conversion can be done both ways      Convert row and column numbers  0-based  to an Excel cell reference CellReference numbers   new CellReference 3  28   System out println numbers formatAsString         Convert an Excel cell reference back into digits CellReference reference   new CellReference  AC4    System out println reference getRow            reference getCol

User · Answer

public static string ConvertToAlphaColumnReferenceFromInteger int columnReference                int baseValue     int   A    - 1           string lsReturn   String Empty            if  columnReference  gt  26                         lsReturn   ConvertToAlphaColumnReferenceFromInteger Convert ToInt32 Convert ToDouble columnReference   26  ToString   Split      0                         return lsReturn   Convert ToChar baseValue    columnReference   26

User · Answer

If anyone needs to do this in Excel without VBA  here is a way    SUBSTITUTE ADDRESS 1 colNum 4   1        where colNum is the column number  And in VBA   Function GetColumnName colNum As Integer  As String     Dim d As Integer     Dim m As Integer     Dim name As String     d   colNum     name          Do While  d  gt  0          m    d - 1  Mod 26         name   Chr 65   m    name         d   Int  d - m    26      Loop     GetColumnName   name End Function

User · Answer

T-SQL  SQL SERVER 18   Copy of the solution on first page  CREATE FUNCTION dbo getExcelColumnNameByOrdinal  RowNum int    RETURNS varchar 5     AS    BEGIN       DECLARE  dividend int    RowNum      DECLARE  columnName varchar max            DECLARE  modulo int       WHILE   dividend  gt  0      BEGIN           SELECT  modulo      dividend - 1    26           SELECT  columnName   CHAR  65    modulo      columnName          SELECT  dividend   CAST    dividend -  modulo    26  as int       END     RETURN          columnName   END

User · Answer

If anyone needs to do this in Excel without VBA  here is a way    SUBSTITUTE ADDRESS 1 colNum 4   1        where colNum is the column number  And in VBA   Function GetColumnName colNum As Integer  As String     Dim d As Integer     Dim m As Integer     Dim name As String     d   colNum     name          Do While  d  gt  0          m    d - 1  Mod 26         name   Chr 65   m    name         d   Int  d - m    26      Loop     GetColumnName   name End Function

User · Answer

Using this in VB Net 2005    Private Function ColumnName ByVal ColumnIndex As Integer  As String     Dim Name As String          Name    New Microsoft Office Interop Owc11 Spreadsheet  Columns Item ColumnIndex  Address False  False  Microsoft Office Interop Owc11 XlReferenceStyle xlA1     Name   Split Name       0      Return Name  End Function

User · Answer

In Delphi  Pascal    function GetExcelColumnName columnNumber  integer   string  var   dividend  modulo  integer  begin   Result          dividend    columnNumber    while dividend  gt  0 do begin     modulo     dividend - 1  mod 26      Result    Chr 65   modulo    Result      dividend     dividend - modulo  div 26    end  end

User · Answer

This is a javascript version according to Graham s code  function  columnNumber        var dividend   columnNumber      var columnName           var modulo       while  dividend  gt  0            modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo    columnName          dividend   parseInt  dividend - modulo    26              return columnName

User · Answer

Objective-C Implementation    - NSString  getColumnName  int n        NSString  name             while  n gt 0         n--       char c    char   A    n 26        name    NSString stringWithFormat    c    c name        n   n 26               return name       SWIFT Implementation   func getColumnName n Int - gt String   var columnName       var index   n  while index gt 0        index--      let char   Character UnicodeScalar 65   index 26        columnName      char   columnName        index   index   26     return columnName      The answer is based on  https   stackoverflow com a 4532562 2231118

User · Answer

Thanks for the answers here   helped me come up with these helper functions for some interaction with the Google Sheets API that i m working on in Elixir Phoenix  here s what i came up with  could probably use some extra validation and error handling   In Elixir   def number to column number  do   cond do      number  gt  0  amp  amp  number  lt   26  - gt        to string   number   64         number  gt  26  - gt        div col   number to column div number - 1  26         remainder   rem number  26        rem col   cond do          remainder    0  - gt            number to column 26          true - gt            number to column remainder        end       div col  lt  gt  rem col     true - gt             end end   And the inverse function    def column to number column  do   column       gt  to charlist       gt  Enum reverse       gt  Enum with index       gt  Enum reduce 0  fn  char  idx   acc  - gt          char - 64     math pow 26 idx     acc     end        gt  round end   And some tests   describe  test excel functions  do    excelTestData    A   1     Z  26     AA   27     AB   28     AZ   52    BA   53     AAA   703      test  column to number  do     Enum each  excelTestData  fn  input  expected result   - gt        actual result   BulkOnboardingController column to number input        assert actual result    expected result     end    end    test  number to column  do     Enum each  excelTestData  fn  expected result  input   - gt        actual result   BulkOnboardingController number to column input        assert actual result    expected result     end    end end

User · Answer

Just throwing in a simple two-line C  implementation using recursion  because all the answers here seem far more complicated than necessary        lt summary gt      Gets the column letter s  corresponding to the given column number       lt  summary gt       lt param name  column  gt The one-based column index  Must be greater than zero  lt  param gt       lt returns gt The desired column letter  or an empty string if the column number was invalid  lt  returns gt  public static string GetColumnLetter int column        if  column  lt  1  return String Empty      return GetColumnLetter  column - 1    26     char   A     column - 1    26

User · Answer

I just had to do this work today  my implementation uses recursion   private static string GetColumnLetter string colNumber        if  string IsNullOrEmpty colNumber                 throw new ArgumentNullException colNumber              string colName   String Empty       try               var colNum   Convert ToInt32 colNumber           var mod   colNum   26          var div   Math Floor  double  colNum  26           colName     div  gt  0    GetColumnLetter  div - 1  ToString      String Empty    Convert ToChar mod   65             finally               colName   colName    String Empty    A    colName             return colName      This considers the number coming as string the the method and the numbers starting in  0   A   0

User · Answer

Just throwing in a simple two-line C  implementation using recursion  because all the answers here seem far more complicated than necessary        lt summary gt      Gets the column letter s  corresponding to the given column number       lt  summary gt       lt param name  column  gt The one-based column index  Must be greater than zero  lt  param gt       lt returns gt The desired column letter  or an empty string if the column number was invalid  lt  returns gt  public static string GetColumnLetter int column        if  column  lt  1  return String Empty      return GetColumnLetter  column - 1    26     char   A     column - 1    26

User · Answer

In perl  for an input of 1  A   27  AA   etc   sub excel colname     my   idx                one-based column number   -- idx                  zero-based column index   my  name         while   idx  gt   0         name    chr ord  A       idx   26         idx     int  idx   26  - 1        return scalar reverse  name

User · Answer

if you just want it for a cell formula without code  here s a formula for it   IF COLUMN   gt  26 CHAR ROUND COLUMN   26 1  64  amp CHAR MOD COLUMN   26  64  CHAR COLUMN   64

User · Answer

public string ToBase26 int number                if  number  lt  0  return String Empty           int remainder   number   26          int value   number   26           return value    0               String Format   0    Convert ToChar 65   remainder                 String Format   0  1    ToBase26 value - 1   Convert ToChar 65   remainder

User · Answer

Microsoft Excel Miniature  Quick-and-Dirty formula   Hi   Here s one way to get the Excel character-column-header from a number      I created a formula for an Excel cell     i e  I took the approach of not using VBA programming    The formula looks at a cell that has a number in it and tells you what the column is -- in letters   In the attached image    I put 1 2 3 etc in the top row all the way out to column ABS  I pasted my formula in the second row all the way out to ABS  My formula looks at row 1 and converts the number to Excel s column header id   My formula works for all numbers out to 702  zz   I did it in this manner to prove that the formula works so you can look at the output from the formula and look at the column header above and easily visually verify that the formula works   -    CONCATENATE MID   abcdefghijklmnopqrstuvwxyz   IF MOD K1 26  0 INT K1 26  1  INT K1 26     1  MID  abcdefghijklmnopqrstuvwxyz  IF MOD K1 26  0 26 MOD K1 26   1     The underscore was there for debugging purposes - to let you know there was an actual space and that it was working correctly   With this formula above -- whatever you put in K1 - the formula will tell you what the column header will be   The formula  in its current form  only goes out to 2 digits  ZZ  but could be modified to add the 3rd letter  ZZZ

User · Answer

These my codes to convert specific number  index start from 1  to Excel Column       public static string NumberToExcelColumn uint number                uint originalNumber   number           uint numChars   1          while  Math Pow 26  numChars   lt  number                        numChars                 if  Math Pow 26  numChars    26  gt   number                                break                                                  string toRet               uint lastValue   0           do                       number -  lastValue               double powerVal   Math Pow 26  numChars - 1               byte thisCharIdx    byte Math Truncate  columnNumber - 1    powerVal               lastValue    int powerVal   thisCharIdx               if  numChars - 2  gt   0                                double powerVal next   Math Pow 26  numChars - 2                   byte thisCharIdx next    byte Math Truncate  columnNumber - lastValue - 1    powerVal next                   int lastValue next    int Math Pow 26  numChars - 2    thisCharIdx next                   if  thisCharIdx next    0  amp  amp  lastValue next    0  amp  amp  powerVal next    26                                        thisCharIdx--                      lastValue    int powerVal   thisCharIdx                                               toRet     char   byte  A    thisCharIdx     numChars  gt  1    -1   0                 numChars--            while  numChars  gt  0            return toRet          My Unit Test        TestMethod      public void Test                 Assert AreEqual  A   NumberToExcelColumn 1            Assert AreEqual  Z   NumberToExcelColumn 26            Assert AreEqual  AA   NumberToExcelColumn 27            Assert AreEqual  AO   NumberToExcelColumn 41            Assert AreEqual  AZ   NumberToExcelColumn 52            Assert AreEqual  BA   NumberToExcelColumn 53            Assert AreEqual  ZZ   NumberToExcelColumn 702            Assert AreEqual  AAA   NumberToExcelColumn 703            Assert AreEqual  ABC   NumberToExcelColumn 731            Assert AreEqual  ACQ   NumberToExcelColumn 771            Assert AreEqual  AYZ   NumberToExcelColumn 1352            Assert AreEqual  AZA   NumberToExcelColumn 1353            Assert AreEqual  AZB   NumberToExcelColumn 1354            Assert AreEqual  BAA   NumberToExcelColumn 1379            Assert AreEqual  CNU   NumberToExcelColumn 2413            Assert AreEqual  GCM   NumberToExcelColumn 4823            Assert AreEqual  MSR   NumberToExcelColumn 9300            Assert AreEqual  OMB   NumberToExcelColumn 10480            Assert AreEqual  ULV   NumberToExcelColumn 14530            Assert AreEqual  XFD   NumberToExcelColumn 16384

User · Answer

Typescript   function lengthToExcelColumn len  number   string        let dividend  number   len      let columnName  string           let modulo  number   0       while  dividend  gt  0            modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo  toString     columnName          dividend   Math floor  dividend - modulo    26             return columnName

User · Answer

Another VBA way  Public Function GetColumnName TargetCell As Range  As String     GetColumnName   Split CStr TargetCell Cells 1  1  Address        1  End Function

User · Answer

I m using this one in VB NET 2003 and it works well     Private Function GetExcelColumnName ByVal aiColNumber As Integer  As String     Dim BaseValue As Integer   Convert ToInt32   A   Chars 0   - 1     Dim lsReturn As String   String Empty      If  aiColNumber  gt  26  Then         lsReturn   GetExcelColumnName Convert ToInt32  Format aiColNumber   26   0 0   Split       0        End If      GetExcelColumnName   lsReturn   Convert ToChar BaseValue    aiColNumber Mod 26   End Function

User · Answer

Though I am late to the game  Graham s answer is far from being optimal  Particularly  you don t have to use the modulo  call ToString   and apply  int  cast  Considering that in most cases in C  world you would start numbering from 0  here is my revision   public static string GetColumnName int index     zero-based       const byte BASE    Z  -  A    1      string name   String Empty       do               name   Convert ToChar  A    index   BASE    name          index   index   BASE - 1            while  index  gt   0        return name

User · Answer

After looking at all the supplied Versions here  i descided to do one myself  using recursion   Here is my vb net Version   Function CL ByVal x As Integer  As String     If x  gt   1 And x  lt   26 Then         CL   Chr x   64      Else         CL   CL  x - x Mod 26    26   amp  Chr  x Mod 26    1   64      End If End Function

User · Answer

private String getColumn int c        String s           do           s    char   A     c   26     s          c    26        while  c--  gt  0       return s      Its not exactly base 26  there is no 0 in the system  If there was   Z  would be followed by  BA  not by  AA

User · Answer

Here is an Actionscript version   private var columnNumbers Array     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         private function getExcelColumnName columnNumber int    String          var dividend int   columnNumber          var columnName String               var modulo int           while  dividend  gt  0                        modulo    dividend - 1    26              columnName   columnNumbers modulo    columnName              dividend   int  dividend - modulo    26                       return columnName

User · Answer

If anyone needs to do this in Excel without VBA  here is a way    SUBSTITUTE ADDRESS 1 colNum 4   1        where colNum is the column number  And in VBA   Function GetColumnName colNum As Integer  As String     Dim d As Integer     Dim m As Integer     Dim name As String     d   colNum     name          Do While  d  gt  0          m    d - 1  Mod 26         name   Chr 65   m    name         d   Int  d - m    26      Loop     GetColumnName   name End Function

User · Answer

Though I am late to the game  Graham s answer is far from being optimal  Particularly  you don t have to use the modulo  call ToString   and apply  int  cast  Considering that in most cases in C  world you would start numbering from 0  here is my revision   public static string GetColumnName int index     zero-based       const byte BASE    Z  -  A    1      string name   String Empty       do               name   Convert ToChar  A    index   BASE    name          index   index   BASE - 1            while  index  gt   0        return name

User · Answer

This answer is in javaScript   function getCharFromNumber columnNumber       var dividend   columnNumber      var columnName           var modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo  toString     columnName          dividend   parseInt  dividend - modulo    26              return  columnName

User · Answer

Here s how I do it   private string GetExcelColumnName int columnNumber        int dividend   columnNumber      string columnName   String Empty      int modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   Convert ToChar 65   modulo  ToString     columnName          dividend    int   dividend - modulo    26               return columnName

User · Answer

A little late to the game  but here s the code I use  in C     private static readonly string  Alphabet    ABCDEFGHIJKLMNOPQRSTUVWXYZ   public static int ColumnNameParse string value           assumes value Length is  1 3         assumes value is uppercase     var digits   value PadLeft 3  Select x   gt   Alphabet IndexOf x        return digits Aggregate 0   current  index    gt   current   26     index   1

User · Answer

I m trying to do the same thing in Java    I ve wrote following code   private String getExcelColumnName int columnNumber         int dividend   columnNumber      String columnName           int modulo       while  dividend  gt  0                modulo    dividend - 1    26           char val   Character valueOf  char  65   modulo             columnName    val           dividend    int   dividend - modulo    26               return columnName      Now once I ran it with columnNumber   29  it gives me the result    CA   instead of  AC   any comments what I m missing  I know I can reverse it by StringBuilder     But looking at the Graham s answer  I m little confused

User · Answer

T-SQL  SQL SERVER 18   Copy of the solution on first page  CREATE FUNCTION dbo getExcelColumnNameByOrdinal  RowNum int    RETURNS varchar 5     AS    BEGIN       DECLARE  dividend int    RowNum      DECLARE  columnName varchar max            DECLARE  modulo int       WHILE   dividend  gt  0      BEGIN           SELECT  modulo      dividend - 1    26           SELECT  columnName   CHAR  65    modulo      columnName          SELECT  dividend   CAST    dividend -  modulo    26  as int       END     RETURN          columnName   END

User · Answer

I just had to do this work today  my implementation uses recursion   private static string GetColumnLetter string colNumber        if  string IsNullOrEmpty colNumber                 throw new ArgumentNullException colNumber              string colName   String Empty       try               var colNum   Convert ToInt32 colNumber           var mod   colNum   26          var div   Math Floor  double  colNum  26           colName     div  gt  0    GetColumnLetter  div - 1  ToString      String Empty    Convert ToChar mod   65             finally               colName   colName    String Empty    A    colName             return colName      This considers the number coming as string the the method and the numbers starting in  0   A   0

User · Answer

Microsoft Excel Miniature  Quick-and-Dirty formula   Hi   Here s one way to get the Excel character-column-header from a number      I created a formula for an Excel cell     i e  I took the approach of not using VBA programming    The formula looks at a cell that has a number in it and tells you what the column is -- in letters   In the attached image    I put 1 2 3 etc in the top row all the way out to column ABS  I pasted my formula in the second row all the way out to ABS  My formula looks at row 1 and converts the number to Excel s column header id   My formula works for all numbers out to 702  zz   I did it in this manner to prove that the formula works so you can look at the output from the formula and look at the column header above and easily visually verify that the formula works   -    CONCATENATE MID   abcdefghijklmnopqrstuvwxyz   IF MOD K1 26  0 INT K1 26  1  INT K1 26     1  MID  abcdefghijklmnopqrstuvwxyz  IF MOD K1 26  0 26 MOD K1 26   1     The underscore was there for debugging purposes - to let you know there was an actual space and that it was working correctly   With this formula above -- whatever you put in K1 - the formula will tell you what the column header will be   The formula  in its current form  only goes out to 2 digits  ZZ  but could be modified to add the 3rd letter  ZZZ

User · Answer

You might need conversion both ways  e g from Excel column adress like AAZ to integer and from any integer to Excel   The two methods below will do just that   Assumes 1 based indexing  first element in your  arrays  are element number 1  No limits on size here  so you can use adresses like ERROR and that would be column number 2613824      public static string ColumnAdress int col      if  col  lt   26         return Convert ToChar col   64  ToString          int div   col   26    int mod   col   26    if  mod    0   mod   26 div--     return ColumnAdress div    ColumnAdress mod      public static int ColumnNumber string colAdress      int   digits   new int colAdress Length     for  int i   0  i  lt  colAdress Length    i          digits i    Convert ToInt32 colAdress i   - 64        int mul 1 int res 0    for  int pos   digits Length - 1  pos  gt   0  --pos          res    digits pos    mul      mul    26        return res

User · Answer

Here s my super late implementation in PHP  This one s recursive  I wrote it just before I found this post  I wanted to see if others had solved this problem already     public function GetColumn  intNumber   strCol   null         if   intNumber  gt  0             intRem     intNumber - 1    26           strCol    this- gt GetColumn intval   intNumber -  intRem    26   sprintf   s s   chr 65    intRem    strCol               return  strCol

User · Answer

This is a common question asked in coding test  it has some constraints  max columns per row  702 output should have row number column name e g  for 703 answer is 2A   note  i have just modified existing code from another answer  here is the code for the same       static string GetExcelColumnName long columnNumber                  max number of column per row         const long maxColPerRow   702            find row number         long rowNum    columnNumber   maxColPerRow             find tierable columns in the row          long dividend   columnNumber -  maxColPerRow   rowNum            string columnName   String Empty           long modulo           while  dividend  gt  0                        modulo    dividend - 1    26              columnName   Convert ToChar 65   modulo  ToString     columnName              dividend    int   dividend - modulo    26                      return rowNum 1  columnName

User · Answer

public static string ConvertToAlphaColumnReferenceFromInteger int columnReference                int baseValue     int   A    - 1           string lsReturn   String Empty            if  columnReference  gt  26                         lsReturn   ConvertToAlphaColumnReferenceFromInteger Convert ToInt32 Convert ToDouble columnReference   26  ToString   Split      0                         return lsReturn   Convert ToChar baseValue    columnReference   26

User · Answer

Same implementaion in Java   public String getExcelColumnName  int columnNumber                      int dividend   columnNumber             int i          String columnName                    int modulo               while  dividend  gt  0                                     modulo    dividend - 1    26                       i   65   modulo              columnName   new Character  char i  toString     columnName                      dividend    int   dividend - modulo    26                                return columnName

User · Answer

JavaScript Solution         Calculate the column letter abbreviation from a 1 based index     param  Number  value     returns  string      getColumnFromIndex   function  value        var base    ABCDEFGHIJKLMNOPQRSTUVWXYZ  split          var remainder  result           do           remainder   value   26          result   base  remainder    26  - 1    result          value   Math floor value   26         while  value  gt  0       return result

User · Answer

Saw one other VBA answer - this can be done in excel-vba with a 1 line UDF    Function GetColLetter ByVal colID As Integer  As String     GetColLetter   Split Cells 1  colID  Address       1  End Function

User · Answer

int nCol   127  string sChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt   26        int nChar   nCol   26      nCol    nCol - nChar    26         You could do some trick with using nChar as offset from  A   but I am lazy to do it right now      sCol   sChars nChar    sCol    sCol   sChars nCol    sCol    Update  Peter s comment is right  That s what I get for writing code in the browser   -  My solution was not compiling  it was missing the left-most letter and it was building the string in reverse order - all now fixed   Bugs aside  the algorithm is basically converting a number from base 10 to base 26   Update 2  Joel Coehoorn is right - the code above will return AB for 27  If it was real base 26 number  AA would be equal to A and the next number after Z would be BA   int nCol   127  string sChars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt  26        int nChar   nCol   26      if  nChar    0          nChar   26      nCol    nCol - nChar    26      sCol   sChars nChar    sCol    if  nCol    0      sCol   sChars nCol    sCol

User · Answer

Here is how I would do it in Python  The algorithm is explained below   alph     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   def labelrec n  res       if n lt 26          return alph n  res     else          rem   n 26         res   alph rem  res         n   n 26-1         return labelrec n  res    The function labelrec can be called with the number and an empty string like   print labelrec 16383        Here is why it works  If decimal numbers were written the same way as Excel sheet columns  number 0-9 would be written normally  but 10 would become  00  and then 20 would become  10  and so on  Mapping few numbers   0 - 0  9 - 9  10  - 00  20 - 10  100 - 90  110 - 000  1110 - 0000  So  the pattern is clear  Starting at the unit s place  if a number is less than 10  it s representation is same as the number itself  else you need to adjust the remaining number by subtracting it by 1 and recurse  You can stop when the number is less than 10   The same logic is applied for numbers of base 26 in above solution   P S  If you want the numbers to begin from 1  call the same function on input number after decreasing it by 1

User · Answer

A little late to the game  but here s the code I use  in C     private static readonly string  Alphabet    ABCDEFGHIJKLMNOPQRSTUVWXYZ   public static int ColumnNameParse string value           assumes value Length is  1 3         assumes value is uppercase     var digits   value PadLeft 3  Select x   gt   Alphabet IndexOf x        return digits Aggregate 0   current  index    gt   current   26     index   1

User · Answer

if you just want it for a cell formula without code  here s a formula for it   IF COLUMN   gt  26 CHAR ROUND COLUMN   26 1  64  amp CHAR MOD COLUMN   26  64  CHAR COLUMN   64

User · Answer

Easy with recursion   public static string GetStandardExcelColumnName int columnNumberOneBased      int baseValue   Convert ToInt32  A      int columnNumberZeroBased   columnNumberOneBased - 1     string ret          if  columnNumberOneBased  gt  26          ret   GetStandardExcelColumnName columnNumberZeroBased   26           return ret   Convert ToChar baseValue    columnNumberZeroBased   26

User · Answer

In perl  for an input of 1  A   27  AA   etc   sub excel colname     my   idx                one-based column number   -- idx                  zero-based column index   my  name         while   idx  gt   0         name    chr ord  A       idx   26         idx     int  idx   26  - 1        return scalar reverse  name

User · Answer

int nCol   127  string sChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt   26        int nChar   nCol   26      nCol    nCol - nChar    26         You could do some trick with using nChar as offset from  A   but I am lazy to do it right now      sCol   sChars nChar    sCol    sCol   sChars nCol    sCol    Update  Peter s comment is right  That s what I get for writing code in the browser   -  My solution was not compiling  it was missing the left-most letter and it was building the string in reverse order - all now fixed   Bugs aside  the algorithm is basically converting a number from base 10 to base 26   Update 2  Joel Coehoorn is right - the code above will return AB for 27  If it was real base 26 number  AA would be equal to A and the next number after Z would be BA   int nCol   127  string sChars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt  26        int nChar   nCol   26      if  nChar    0          nChar   26      nCol    nCol - nChar    26      sCol   sChars nChar    sCol    if  nCol    0      sCol   sChars nCol    sCol

User · Answer

NodeJS implementation         getColumnFromIndex   Helper that returns a column value  A-XFD  for an index value  integer     The column follows the Common Spreadsheet Format e g   A  AA  AAA    See https   stackoverflow com questions 181596 how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa 3444285 3444285    param numVal  Integer    return String    getColumnFromIndex  function numVal      var dividend   parseInt numVal      var columnName          var modulo     while  dividend  gt  0          modulo    dividend - 1    26        columnName   String fromCharCode 65   modulo    columnName        dividend   parseInt  dividend - modulo    26           return columnName       Thanks to Convert excel column alphabet  e g  AA  to number  e g   25   And in reverse         getIndexFromColumn   Helper that returns an index value  integer  for a column value  A-XFD     The column follows the Common Spreadsheet Format e g   A  AA  AAA    See https   stackoverflow com questions 9905533 convert-excel-column-alphabet-e-g-aa-to-number-e-g-25    param strVal  String    return Integer    getIndexFromColumn  function val      var base    ABCDEFGHIJKLMNOPQRSTUVWXYZ   i  j  result   0     for  i   0  j   val length - 1  i  lt  val length  i    1  j -  1          result    Math pow base length  j     base indexOf val i     1           return result

User · Answer

Objective-C Implementation    - NSString  getColumnName  int n        NSString  name             while  n gt 0         n--       char c    char   A    n 26        name    NSString stringWithFormat    c    c name        n   n 26               return name       SWIFT Implementation   func getColumnName n Int - gt String   var columnName       var index   n  while index gt 0        index--      let char   Character UnicodeScalar 65   index 26        columnName      char   columnName        index   index   26     return columnName      The answer is based on  https   stackoverflow com a 4532562 2231118

User · Answer

I wanted to throw in my static class I use  for interoping between col index and col Label  I use a modified accepted answer for my ColumnLabel Method  public static class Extensions       public static string ColumnLabel this int col                var dividend   col          var columnLabel   string Empty          int modulo           while  dividend  gt  0                        modulo    dividend - 1    26              columnLabel   Convert ToChar 65   modulo  ToString     columnLabel              dividend    int   dividend - modulo    26                       return columnLabel            public static int ColumnIndex this string colLabel                    AD   1   26 1     4   26 0              var colIndex   0          for int ind   0  pow   colLabel Count  -1  ind  lt  colLabel Count      ind  --pow                        var cVal   Convert ToInt32 colLabel ind   - 64    col A is index 1             colIndex    cVal     int Math Pow 26  pow                      return colIndex            Use this like     30 ColumnLabel        AD   AD  ColumnIndex       30

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def excel column number to name column number       output          index   column number-1     while index  gt   0          character   chr  index 26  ord  A            output   output   character         index   index 26 - 1      return output   -1    for i in xrange 1  1024       print   4d    s     i  excel column number to name i     Passed these test cases    Column Number  494286     ABCDZ  Column Number  27    AA Column Number  52    AZ

User · Answer

After looking at all the supplied Versions here  i descided to do one myself  using recursion   Here is my vb net Version   Function CL ByVal x As Integer  As String     If x  gt   1 And x  lt   26 Then         CL   Chr x   64      Else         CL   CL  x - x Mod 26    26   amp  Chr  x Mod 26    1   64      End If End Function

User · Answer

Here is a simpler solution for zero based column Index  public static string GetColumnIndexNumberToExcelColumn int columnIndex                        int offset   columnIndex   26              int multiple   columnIndex   26               int initialSeed   65   Represents column  quot A quot              if  multiple    0                                return Convert ToChar initialSeed   offset  ToString                               return   quot  Convert ToChar initialSeed   multiple - 1   Convert ToChar initialSeed   offset   quot

User · Answer

If anyone needs to do this in Excel without VBA  here is a way    SUBSTITUTE ADDRESS 1 colNum 4   1        where colNum is the column number  And in VBA   Function GetColumnName colNum As Integer  As String     Dim d As Integer     Dim m As Integer     Dim name As String     d   colNum     name          Do While  d  gt  0          m    d - 1  Mod 26         name   Chr 65   m    name         d   Int  d - m    26      Loop     GetColumnName   name End Function

User · Answer

Another solution   private void Foo        l ExcelApp   new Excel ApplicationClass       l ExcelApp ReferenceStyle   Excel XlReferenceStyle xlR1C1            now reference by R row C column   Ex  A1  lt    gt  R1C1  C6  lt    gt  R3C6          see more here - Cell referencing in Excel for everyone  by Dr Nitin Paranjape

User · Answer

Seems like so many answers are much more complex than necessary  Here is a generic Ruby answer based on the recursion described above   One nice thing about this answer is that it s not limited to the 26 characters of English Alphabet  You can define any range you like in COLUMNS constant and it will do the right thing       vim  ft ruby   class Numeric     COLUMNS     A    Z   to a      def to excel column n   self        n  lt  1         begin         base   COLUMNS size         to excel column  n - 1    base    COLUMNS  n - 1    base        end     end   end      verify     1  52  each    i  printf   4d   gt   4s n   i  i to excel column     This prints the following  eg      1   gt     A    2   gt     B    3   gt     C          33   gt    AG   34   gt    AH   35   gt    AI   36   gt    AJ   37   gt    AK   38   gt    AL   39   gt    AM   40   gt    AN   41   gt    AO   42   gt    AP   43   gt    AQ   44   gt    AR   45   gt    AS   46   gt    AT   47   gt    AU   48   gt    AV   49   gt    AW   50   gt    AX   51   gt    AY   52   gt    AZ

User · Answer

Refining the original solution  in C     public static class ExcelHelper       private static Dictionary lt UInt16  String gt  l DictionaryOfColumns       public static ExcelHelper             l DictionaryOfColumns   new Dictionary lt ushort  string gt  256              public static String GetExcelColumnName UInt16 l Column                UInt16 l ColumnCopy   l Column          String l Chars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ           String l rVal               UInt16 l Char            if  l DictionaryOfColumns ContainsKey l Column     true                        l rVal   l DictionaryOfColumns l Column                     else                       while  l ColumnCopy  gt  26                                l Char   l ColumnCopy   26                  if  l Char    0                      l Char   26                   l ColumnCopy    l ColumnCopy - l Char    26                  l rVal   l Chars l Char    l rVal                            if  l ColumnCopy    0                  l rVal   l Chars l ColumnCopy    l rVal               l DictionaryOfColumns ContainsKey l Column    l rVal                     return l rVal

User · Answer

Here s how I do it   private string GetExcelColumnName int columnNumber        int dividend   columnNumber      string columnName   String Empty      int modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   Convert ToChar 65   modulo  ToString     columnName          dividend    int   dividend - modulo    26               return columnName

User · Answer

Coincise and elegant Ruby version   def col name col idx      name          while col idx gt 0         mod        col idx-1  26         name       65 mod  chr   name         col idx     col idx-mod  26  to i     end     name end

User · Answer

This is a javascript version according to Graham s code  function  columnNumber        var dividend   columnNumber      var columnName           var modulo       while  dividend  gt  0            modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo    columnName          dividend   parseInt  dividend - modulo    26              return columnName

User · Answer

I discovered an error in my first post  so I decided to sit down and do the the math   What I found is that the number system used to identify Excel columns is not a base 26 system  as another person posted   Consider the following in base 10   You can also do this with the letters of the alphabet   Space                          S1  S2  S3   S1  S2  S3                                     0  00  000     A  AA  AAA                                     1  01  001     B  AB  AAB                                                                                                          9  99  999     Z  ZZ  ZZZ Total states in space     10  100  1000   26  676  17576 Total States                1110                18278  Excel numbers columns in the individual alphabetical spaces using base 26   You can see that in general  the state space progression is a  a 2  a 3      for some base a  and the total number of states is a   a 2   a 3           Suppose you want to find the total number of states A in the first N spaces   The formula for doing so is A    a  a N - 1    a-1    This is important because we need to find the space N that corresponds to our index K   If I want to find out where K lies in the number system I need to replace A with K and solve for N   The solution is N   log base a   A  a-1  a  1    If I use the example of a   10 and K   192  I know that N   2 23804       This tells me that K lies at the beginning of the third space since it is a little greater than two   The next step is to find exactly how far in the current space we are   To find this  subtract from K the A generated using the floor of N   In this example  the floor of N is two   So  A    10  10 2     1   10-1    110  as is expected when you combine the states of the first two spaces   This needs to be subtracted from K because these first 110 states would have already been accounted for in the first two spaces   This leaves us with 82 states   So  in this number system  the representation of 192 in base 10 is 082     The C  code using a base index of zero is      private string ExcelColumnIndexToName int Index                string range   string Empty          if  Index  lt  0   return range          int a   26          int x    int Math Floor Math Log  Index     a - 1    a   1  a            Index -   int  Math Pow a  x  - 1    a    a - 1           for  int i   x 1  Index   i  gt  0  i--                        range     char  65   Index   a   ToString     range              Index    a                    return range            Old Post  A zero-based solution in C        private string ExcelColumnIndexToName int Index                string range               if  Index  lt  0   return range          for int i 1 Index   i  gt  0 i 0                        range     char  65   Index   26   ToString     range              Index    26                    if  range Length  gt  1  range     char   int range 0  - 1   ToString     range Substring 1           return range

User · Answer

Thanks for the answers here   helped me come up with these helper functions for some interaction with the Google Sheets API that i m working on in Elixir Phoenix  here s what i came up with  could probably use some extra validation and error handling   In Elixir   def number to column number  do   cond do      number  gt  0  amp  amp  number  lt   26  - gt        to string   number   64         number  gt  26  - gt        div col   number to column div number - 1  26         remainder   rem number  26        rem col   cond do          remainder    0  - gt            number to column 26          true - gt            number to column remainder        end       div col  lt  gt  rem col     true - gt             end end   And the inverse function    def column to number column  do   column       gt  to charlist       gt  Enum reverse       gt  Enum with index       gt  Enum reduce 0  fn  char  idx   acc  - gt          char - 64     math pow 26 idx     acc     end        gt  round end   And some tests   describe  test excel functions  do    excelTestData    A   1     Z  26     AA   27     AB   28     AZ   52    BA   53     AAA   703      test  column to number  do     Enum each  excelTestData  fn  input  expected result   - gt        actual result   BulkOnboardingController column to number input        assert actual result    expected result     end    end    test  number to column  do     Enum each  excelTestData  fn  expected result  input   - gt        actual result   BulkOnboardingController number to column input        assert actual result    expected result     end    end end

User · Answer

Here is my solution in python   import math  num   3500 row number   str math ceil num   702   letters      num   num - 702   math floor num   702  while num      mod    num - 1    26     letters    chr mod   65      num    num - 1     26 result   row number       join reversed letters    print result

User · Answer

I m using this one in VB NET 2003 and it works well     Private Function GetExcelColumnName ByVal aiColNumber As Integer  As String     Dim BaseValue As Integer   Convert ToInt32   A   Chars 0   - 1     Dim lsReturn As String   String Empty      If  aiColNumber  gt  26  Then         lsReturn   GetExcelColumnName Convert ToInt32  Format aiColNumber   26   0 0   Split       0        End If      GetExcelColumnName   lsReturn   Convert ToChar BaseValue    aiColNumber Mod 26   End Function

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def ColIdxToXlName idx       if idx  lt  1          raise ValueError  Index is too small       result          while True          if idx  gt  26              idx  r   divmod idx - 1  26              result   chr r   ord  A      result         else              return chr idx   ord  A   - 1    result   for i in xrange 1  1024       print   4d    s     i  ColIdxToXlName i

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def ColIdxToXlName idx       if idx  lt  1          raise ValueError  Index is too small       result          while True          if idx  gt  26              idx  r   divmod idx - 1  26              result   chr r   ord  A      result         else              return chr idx   ord  A   - 1    result   for i in xrange 1  1024       print   4d    s     i  ColIdxToXlName i

User · Answer

private String getColumn int c        String s           do           s    char   A     c   26     s          c    26        while  c--  gt  0       return s      Its not exactly base 26  there is no 0 in the system  If there was   Z  would be followed by  BA  not by  AA

User · Answer

Here is how I would do it in Python  The algorithm is explained below   alph     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   def labelrec n  res       if n lt 26          return alph n  res     else          rem   n 26         res   alph rem  res         n   n 26-1         return labelrec n  res    The function labelrec can be called with the number and an empty string like   print labelrec 16383        Here is why it works  If decimal numbers were written the same way as Excel sheet columns  number 0-9 would be written normally  but 10 would become  00  and then 20 would become  10  and so on  Mapping few numbers   0 - 0  9 - 9  10  - 00  20 - 10  100 - 90  110 - 000  1110 - 0000  So  the pattern is clear  Starting at the unit s place  if a number is less than 10  it s representation is same as the number itself  else you need to adjust the remaining number by subtracting it by 1 and recurse  You can stop when the number is less than 10   The same logic is applied for numbers of base 26 in above solution   P S  If you want the numbers to begin from 1  call the same function on input number after decreasing it by 1

User · Answer

I realise the question relates to C  however  if anyone reading needs to do the same thing with Java then the following may be useful   It turns out that this can easily be done using the the  CellReference  class in Jakarta POI  Also  the conversion can be done both ways      Convert row and column numbers  0-based  to an Excel cell reference CellReference numbers   new CellReference 3  28   System out println numbers formatAsString         Convert an Excel cell reference back into digits CellReference reference   new CellReference  AC4    System out println reference getRow            reference getCol

User · Answer

Seems like so many answers are much more complex than necessary  Here is a generic Ruby answer based on the recursion described above   One nice thing about this answer is that it s not limited to the 26 characters of English Alphabet  You can define any range you like in COLUMNS constant and it will do the right thing       vim  ft ruby   class Numeric     COLUMNS     A    Z   to a      def to excel column n   self        n  lt  1         begin         base   COLUMNS size         to excel column  n - 1    base    COLUMNS  n - 1    base        end     end   end      verify     1  52  each    i  printf   4d   gt   4s n   i  i to excel column     This prints the following  eg      1   gt     A    2   gt     B    3   gt     C          33   gt    AG   34   gt    AH   35   gt    AI   36   gt    AJ   37   gt    AK   38   gt    AL   39   gt    AM   40   gt    AN   41   gt    AO   42   gt    AP   43   gt    AQ   44   gt    AR   45   gt    AS   46   gt    AT   47   gt    AU   48   gt    AV   49   gt    AW   50   gt    AX   51   gt    AY   52   gt    AZ

User · Answer

If you are wanting to reference the cell progmatically then you will get much more readable code if you use the Cells method of a sheet   It takes a row and column index instead of a traditonal cell reference   It is very similar to the Offset method

User · Answer

Here s how I do it   private string GetExcelColumnName int columnNumber        int dividend   columnNumber      string columnName   String Empty      int modulo       while  dividend  gt  0                modulo    dividend - 1    26          columnName   Convert ToChar 65   modulo  ToString     columnName          dividend    int   dividend - modulo    26               return columnName

User · Answer

And converted to php   function GetExcelColumnName  columnNumber         columnName           while   columnNumber  gt  0             modulo     columnNumber - 1    26           columnName   chr 65    modulo     columnName           columnNumber    int    columnNumber -  modulo    26             return  columnName

User · Answer

F  version of each way  let rec getExcelColumnName x    if x lt 26 then int  A  x  gt char  gt string else  x 26-1  gt c   c x 26    pardon the minimizing  was working on a better version of https   stackoverflow com a 4500043 57883   and the opposite direction      return values start at 0 let getIndexFromExcelColumnName  x string        let a   int  A      let fPow len i           Math Pow 26   len - 1 - i   gt  float            gt  int      let getValue len i c            int c - a   1   fPow len i     let f i   getValue x Length i x  i       0    x Length - 1        gt  Seq map f       gt  Seq sum       gt  fun x - gt  x - 1

User · Answer

I m trying to do the same thing in Java    I ve wrote following code   private String getExcelColumnName int columnNumber         int dividend   columnNumber      String columnName           int modulo       while  dividend  gt  0                modulo    dividend - 1    26           char val   Character valueOf  char  65   modulo             columnName    val           dividend    int   dividend - modulo    26               return columnName      Now once I ran it with columnNumber   29  it gives me the result    CA   instead of  AC   any comments what I m missing  I know I can reverse it by StringBuilder     But looking at the Graham s answer  I m little confused

User · Answer

private String getColumn int c        String s           do           s    char   A     c   26     s          c    26        while  c--  gt  0       return s      Its not exactly base 26  there is no 0 in the system  If there was   Z  would be followed by  BA  not by  AA

User · Answer

public string ToBase26 int number                if  number  lt  0  return String Empty           int remainder   number   26          int value   number   26           return value    0               String Format   0    Convert ToChar 65   remainder                 String Format   0  1    ToBase26 value - 1   Convert ToChar 65   remainder

User · Answer

Another solution   private void Foo        l ExcelApp   new Excel ApplicationClass       l ExcelApp ReferenceStyle   Excel XlReferenceStyle xlR1C1            now reference by R row C column   Ex  A1  lt    gt  R1C1  C6  lt    gt  R3C6          see more here - Cell referencing in Excel for everyone  by Dr Nitin Paranjape

User · Answer

I m using this one in VB NET 2003 and it works well     Private Function GetExcelColumnName ByVal aiColNumber As Integer  As String     Dim BaseValue As Integer   Convert ToInt32   A   Chars 0   - 1     Dim lsReturn As String   String Empty      If  aiColNumber  gt  26  Then         lsReturn   GetExcelColumnName Convert ToInt32  Format aiColNumber   26   0 0   Split       0        End If      GetExcelColumnName   lsReturn   Convert ToChar BaseValue    aiColNumber Mod 26   End Function

User · Answer

And converted to php   function GetExcelColumnName  columnNumber         columnName           while   columnNumber  gt  0             modulo     columnNumber - 1    26           columnName   chr 65    modulo     columnName           columnNumber    int    columnNumber -  modulo    26             return  columnName

User · Answer

These my codes to convert specific number  index start from 1  to Excel Column       public static string NumberToExcelColumn uint number                uint originalNumber   number           uint numChars   1          while  Math Pow 26  numChars   lt  number                        numChars                 if  Math Pow 26  numChars    26  gt   number                                break                                                  string toRet               uint lastValue   0           do                       number -  lastValue               double powerVal   Math Pow 26  numChars - 1               byte thisCharIdx    byte Math Truncate  columnNumber - 1    powerVal               lastValue    int powerVal   thisCharIdx               if  numChars - 2  gt   0                                double powerVal next   Math Pow 26  numChars - 2                   byte thisCharIdx next    byte Math Truncate  columnNumber - lastValue - 1    powerVal next                   int lastValue next    int Math Pow 26  numChars - 2    thisCharIdx next                   if  thisCharIdx next    0  amp  amp  lastValue next    0  amp  amp  powerVal next    26                                        thisCharIdx--                      lastValue    int powerVal   thisCharIdx                                               toRet     char   byte  A    thisCharIdx     numChars  gt  1    -1   0                 numChars--            while  numChars  gt  0            return toRet          My Unit Test        TestMethod      public void Test                 Assert AreEqual  A   NumberToExcelColumn 1            Assert AreEqual  Z   NumberToExcelColumn 26            Assert AreEqual  AA   NumberToExcelColumn 27            Assert AreEqual  AO   NumberToExcelColumn 41            Assert AreEqual  AZ   NumberToExcelColumn 52            Assert AreEqual  BA   NumberToExcelColumn 53            Assert AreEqual  ZZ   NumberToExcelColumn 702            Assert AreEqual  AAA   NumberToExcelColumn 703            Assert AreEqual  ABC   NumberToExcelColumn 731            Assert AreEqual  ACQ   NumberToExcelColumn 771            Assert AreEqual  AYZ   NumberToExcelColumn 1352            Assert AreEqual  AZA   NumberToExcelColumn 1353            Assert AreEqual  AZB   NumberToExcelColumn 1354            Assert AreEqual  BAA   NumberToExcelColumn 1379            Assert AreEqual  CNU   NumberToExcelColumn 2413            Assert AreEqual  GCM   NumberToExcelColumn 4823            Assert AreEqual  MSR   NumberToExcelColumn 9300            Assert AreEqual  OMB   NumberToExcelColumn 10480            Assert AreEqual  ULV   NumberToExcelColumn 14530            Assert AreEqual  XFD   NumberToExcelColumn 16384

User · Answer

More than 30 solutions already  but here s my one-line C  solution      public string IntToExcelColumn int i        return   i lt 16926         char     i 26 -1  26  65   ToString       i lt 2730         char     i 26 -1  26  65   ToString       i lt 26         char     i 26 -1  26  65   ToString        char   i 26  65

User · Answer

Same implementaion in Java   public String getExcelColumnName  int columnNumber                      int dividend   columnNumber             int i          String columnName                    int modulo               while  dividend  gt  0                                     modulo    dividend - 1    26                       i   65   modulo              columnName   new Character  char i  toString     columnName                      dividend    int   dividend - modulo    26                                return columnName

User · Answer

Typescript   function lengthToExcelColumn len  number   string        let dividend  number   len      let columnName  string           let modulo  number   0       while  dividend  gt  0            modulo    dividend - 1    26          columnName   String fromCharCode 65   modulo  toString     columnName          dividend   Math floor  dividend - modulo    26             return columnName

User · Answer

Here is my solution in python   import math  num   3500 row number   str math ceil num   702   letters      num   num - 702   math floor num   702  while num      mod    num - 1    26     letters    chr mod   65      num    num - 1     26 result   row number       join reversed letters    print result

User · Answer

int nCol   127  string sChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt   26        int nChar   nCol   26      nCol    nCol - nChar    26         You could do some trick with using nChar as offset from  A   but I am lazy to do it right now      sCol   sChars nChar    sCol    sCol   sChars nCol    sCol    Update  Peter s comment is right  That s what I get for writing code in the browser   -  My solution was not compiling  it was missing the left-most letter and it was building the string in reverse order - all now fixed   Bugs aside  the algorithm is basically converting a number from base 10 to base 26   Update 2  Joel Coehoorn is right - the code above will return AB for 27  If it was real base 26 number  AA would be equal to A and the next number after Z would be BA   int nCol   127  string sChars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt  26        int nChar   nCol   26      if  nChar    0          nChar   26      nCol    nCol - nChar    26      sCol   sChars nChar    sCol    if  nCol    0      sCol   sChars nCol    sCol

User · Answer

More than 30 solutions already  but here s my one-line C  solution      public string IntToExcelColumn int i        return   i lt 16926         char     i 26 -1  26  65   ToString       i lt 2730         char     i 26 -1  26  65   ToString       i lt 26         char     i 26 -1  26  65   ToString        char   i 26  65

User · Answer

Easy with recursion   public static string GetStandardExcelColumnName int columnNumberOneBased      int baseValue   Convert ToInt32  A      int columnNumberZeroBased   columnNumberOneBased - 1     string ret          if  columnNumberOneBased  gt  26          ret   GetStandardExcelColumnName columnNumberZeroBased   26           return ret   Convert ToChar baseValue    columnNumberZeroBased   26

User · Answer

This is the question all others as well as Google redirect to so I m posting this here   Many of these answers are correct but too cumbersome for simple situations such as when you don t have over 26 columns  If you have any doubt whether you might go into double character columns then ignore this answer  but if you re sure you won t  then you could do it as simple as this in C    public static char ColIndexToLetter short index        if  index  lt  0    index  gt  25  throw new ArgumentException  Index must be between 0 and 25         return  char   A    index       Heck  if you re confident about what you re passing in you could even remove the validation and use this inline    char   A    index    This will be very similar in many languages so you can adapt it as needed   Again  only use this if you re 100  sure you won t have more than 26 columns

User · Answer

Using this in VB Net 2005    Private Function ColumnName ByVal ColumnIndex As Integer  As String     Dim Name As String          Name    New Microsoft Office Interop Owc11 Spreadsheet  Columns Item ColumnIndex  Address False  False  Microsoft Office Interop Owc11 XlReferenceStyle xlA1     Name   Split Name       0      Return Name  End Function

User · Answer

I m surprised all of the solutions so far contain either iteration or recursion   Here s my solution that runs in constant time  no loops    This solution works for all possible Excel columns and checks that the input can be turned into an Excel column   Possible columns are in the range  A  XFD  or  1  16384     This is dependent on your version of Excel   private static string Turn uint col        if  col  lt  1    col  gt  16384    Excel columns are one-based  one    A           throw new ArgumentException  col must be  gt   1 and  lt   16384         if  col  lt   26    one character         return   char  col    A  - 1   ToString         else if  col  lt   702    two characters               char firstChar    char   int   col - 1    26     A  - 1           char secondChar    char  col   26    A  - 1            if  secondChar           Excel is one-based  but modulo operations are zero-based             secondChar    Z     convert one-based to zero-based          return string Format   0  1    firstChar  secondChar              else   three characters               char firstChar    char   int   col - 1    702     A  - 1           char secondChar    char   col - 1    26   26    A  - 1           char thirdChar    char  col   26    A  - 1            if  thirdChar           Excel is one-based  but modulo operations are zero-based             thirdChar    Z     convert one-based to zero-based          return string Format   0  1  2    firstChar  secondChar  thirdChar

User · Answer

Most of previous answers are correct  Here is one more way of converting column number to excel columns  solution is rather simple if we think about this as a base conversion  Simply  convert the column number to base 26 since there is 26 letters only  Here is how you can do this      steps    set the column as a  quotient subtract one from quotient variable  from previous step  because we need to end up on ascii table with 97 being a  divide by 26 and get the remainder  add  97 to remainder and convert to char  since 97 is  a  in ASCII table  quotient becomes the new quotient  26  since we might go over 26 column  continue to do this until quotient is greater than 0 and then return the result   here is the code that does this     def convert num to column column num       result          quotient   column num     remainder   0     while  quotient  gt 0           quotient   quotient -1         remainder   quotient 26         result   chr int remainder  97  result         quotient   int quotient 26      return result  print  --  convert num to column 1  upper

User · Answer

int nCol   127  string sChars    ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt   26        int nChar   nCol   26      nCol    nCol - nChar    26         You could do some trick with using nChar as offset from  A   but I am lazy to do it right now      sCol   sChars nChar    sCol    sCol   sChars nCol    sCol    Update  Peter s comment is right  That s what I get for writing code in the browser   -  My solution was not compiling  it was missing the left-most letter and it was building the string in reverse order - all now fixed   Bugs aside  the algorithm is basically converting a number from base 10 to base 26   Update 2  Joel Coehoorn is right - the code above will return AB for 27  If it was real base 26 number  AA would be equal to A and the next number after Z would be BA   int nCol   127  string sChars    0ABCDEFGHIJKLMNOPQRSTUVWXYZ   string sCol       while  nCol  gt  26        int nChar   nCol   26      if  nChar    0          nChar   26      nCol    nCol - nChar    26      sCol   sChars nChar    sCol    if  nCol    0      sCol   sChars nCol    sCol

User · Answer

If you are wanting to reference the cell progmatically then you will get much more readable code if you use the Cells method of a sheet   It takes a row and column index instead of a traditonal cell reference   It is very similar to the Offset method

User · Answer

Most of previous answers are correct  Here is one more way of converting column number to excel columns  solution is rather simple if we think about this as a base conversion  Simply  convert the column number to base 26 since there is 26 letters only  Here is how you can do this      steps    set the column as a  quotient subtract one from quotient variable  from previous step  because we need to end up on ascii table with 97 being a  divide by 26 and get the remainder  add  97 to remainder and convert to char  since 97 is  a  in ASCII table  quotient becomes the new quotient  26  since we might go over 26 column  continue to do this until quotient is greater than 0 and then return the result   here is the code that does this     def convert num to column column num       result          quotient   column num     remainder   0     while  quotient  gt 0           quotient   quotient -1         remainder   quotient 26         result   chr int remainder  97  result         quotient   int quotient 26      return result  print  --  convert num to column 1  upper

User · Answer

I m surprised all of the solutions so far contain either iteration or recursion   Here s my solution that runs in constant time  no loops    This solution works for all possible Excel columns and checks that the input can be turned into an Excel column   Possible columns are in the range  A  XFD  or  1  16384     This is dependent on your version of Excel   private static string Turn uint col        if  col  lt  1    col  gt  16384    Excel columns are one-based  one    A           throw new ArgumentException  col must be  gt   1 and  lt   16384         if  col  lt   26    one character         return   char  col    A  - 1   ToString         else if  col  lt   702    two characters               char firstChar    char   int   col - 1    26     A  - 1           char secondChar    char  col   26    A  - 1            if  secondChar           Excel is one-based  but modulo operations are zero-based             secondChar    Z     convert one-based to zero-based          return string Format   0  1    firstChar  secondChar              else   three characters               char firstChar    char   int   col - 1    702     A  - 1           char secondChar    char   col - 1    26   26    A  - 1           char thirdChar    char  col   26    A  - 1            if  thirdChar           Excel is one-based  but modulo operations are zero-based             thirdChar    Z     convert one-based to zero-based          return string Format   0  1  2    firstChar  secondChar  thirdChar

User · Answer

Sorry  this is Python instead of C   but at least the results are correct   def ColIdxToXlName idx       if idx  lt  1          raise ValueError  Index is too small       result          while True          if idx  gt  26              idx  r   divmod idx - 1  26              result   chr r   ord  A      result         else              return chr idx   ord  A   - 1    result   for i in xrange 1  1024       print   4d    s     i  ColIdxToXlName i

User · Answer

Here s my super late implementation in PHP  This one s recursive  I wrote it just before I found this post  I wanted to see if others had solved this problem already     public function GetColumn  intNumber   strCol   null         if   intNumber  gt  0             intRem     intNumber - 1    26           strCol    this- gt GetColumn intval   intNumber -  intRem    26   sprintf   s s   chr 65    intRem    strCol               return  strCol

User · Answer

For what it is worth  here is Graham s code in Powershell   function ConvertTo-ExcelColumnID   param        parameter Position   0          HelpMessage    A 1-based index to convert to an excel column ID  e g  2   gt   B   29   gt   AC            Mandatory    true        int  index      string  result       if   index -le 0         return  result     while   index -gt 0         int  modulo     index - 1    26       character    char   modulo    int  char  A         result    character    result       int  index     index -  modulo    26     return  result

[c#] How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Examples related to c#

Examples related to excel