Excel: Column Number to Letter(s)

• Rob Lally
  2:39 pm on November 21st, 2005 1

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

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

  This internet thing rocks!!

  Thanks Craig.

• James Owen
  3:33 pm on August 16th, 2006 2

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

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

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

• James Parsons
  1:08 am on July 10th, 2007 3

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

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

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

• Wissam Kanaan
  10:44 am on July 16th, 2007 4

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

  public string ExcelColumnLetter(int intCol)
  {

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

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

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

• Xanlle
  10:21 am on October 3rd, 2007 5

  Real C# Conversion:

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

• Adam
  2:55 pm on October 9th, 2007 6

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

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

• Joel
  12:37 am on February 8th, 2008 7

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

• Mike
  3:56 pm on April 17th, 2008 8

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

• moustafa mohammed
  10:01 am on July 1st, 2008 9

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

  thanks in advance
  any help is appreciated

• eli
  6:12 am on July 3rd, 2008 10

  dim strCol as string
  dim intCol as integer

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

• Dirk
  10:39 am on July 22nd, 2008 11

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

• Darshan
  10:22 am on September 17th, 2008 12

  public string ExcelColumnLetter(int intCol)

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

  thx

• Gabriel
  9:39 pm on November 18th, 2008 13

  ADAM! Thank you very much.

  In spanish and uppercase:

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

• Marcus Hansson
  12:10 pm on January 31st, 2009 14

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

  Function fnColumnNumberToLetter(ByVal lColNum As Long) As String

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

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

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

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

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

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

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

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

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

  End Function

• MattEvans16
  2:59 am on February 3rd, 2009 15

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

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

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

• MasterEvilAce
  4:08 am on February 3rd, 2009 16

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

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

• Beginner
  7:13 pm on February 17th, 2009 17

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

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

  TempColumnAddress = ActiveCell.Address
  TempColumnID = TempColumnAddress

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

• hinesh
  2:42 pm on April 23rd, 2009 18

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

• incognito
  2:51 am on September 20th, 2009 19

  how to convert over 2147483647

• JJ
  8:20 pm on December 16th, 2009 20

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

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

• JJ
  8:24 pm on December 16th, 2009 21

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

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

  ColNum = ColumnNumber
  ColLetters = “”

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

  ColNumToAlpha = ColLetters

  End Function

• JJ
  8:34 pm on December 16th, 2009 22

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

  Sub TestColNumToLetter()
  Dim TestNum As Integer

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

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

  End Sub

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

• Haha
  3:18 pm on March 1st, 2010 23

  Here is the quick way:

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

• Daniel
  11:32 am on March 9th, 2010 24

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

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

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

  Result := ColLetters;
  end;

• Alan Reddan - Silverarm
  10:23 pm on April 29th, 2010 25

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

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

• surinder
  6:21 pm on May 7th, 2010 26

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

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

• Brad
  8:25 pm on July 15th, 2010 27

  MattEvans16:

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

  What does that even mean?

  And why does everyone keep repeating that?

RSS feed for comments on this post | TrackBack URI