Function to convert column number to letter

Question

Does anyone have an Excel VBA function which can return the column letter s  from a number   For example  entering 100 should return CV

User · Answer

Column letter from column number can be extracted using formula by following steps
1. Calculate the column address using ADDRESS formula
2. Extract the column letter using MID and FIND function

Example:
1. ADDRESS(1000,1000,1)
results $ALL$1000
2. =MID(F15,2,FIND("$",F15,2)-2)
results ALL asuming F15 contains result of step 1

In one go we can write
MID(ADDRESS(1000,1000,1),2,FIND("$",ADDRESS(1000,1000,1),2)-2)

User · Answer

Just one more way to do this  Brettdj s answer made me think of this  but if you use this method you don t have to use a variant array  you can go directly to a string   ColLtr   Cells 1  ColNum  Address True  False  ColLtr   Replace ColLtr    1         or can make it a little more compact with this  ColLtr   Replace Cells 1  ColNum  Address True  False     1         Notice this does depend on you referencing row 1 in the cells object

User · Answer

The solution from brettdj works fantastically  but if you are coming across this as a potential solution for the same reason I was  I thought that I would offer my alternative solution   The problem I was having was scrolling to a specific column based on the output of a MATCH   function  Instead of converting the column number to its column letter parallel  I chose to temporarily toggle the reference style from A1 to R1C1  This way I could just scroll to the column number without having to muck with a VBA function  To easily toggle between the two reference styles  you can use this VBA code   Sub toggle reference style    If Application ReferenceStyle   xlR1C1 Then   Application ReferenceStyle   xlA1 Else   Application ReferenceStyle   xlR1C1 End If  End Sub

User · Answer

For example  MsgBox Columns  9347   Address returns       To return ONLY the column letter s   Split  Columns Column Index  Address  0        0    For example  MsgBox Split  Columns  2734   Address  0        0  returns

User · Answer

This is available through using a formula    SUBSTITUTE ADDRESS 1 COLUMN   4   1        and so also can be written as a VBA function as requested   Function ColName colNum As Integer  As String     ColName   Split Worksheets 1  Cells 1  colNum  Address       1  End Function

User · Answer

So I m late to the party here  but I want to contribute another answer that no one else has addressed yet that doesn t involve arrays  You can do it with simple string manipulation   Function ColLetter Col Index As Long  As String      Dim ColumnLetter As String       Prevent errors  if you get back a number when expecting a letter            you know you did something wrong      If Col Index  lt   0 Or Col Index  gt   16384 Then         ColLetter   0         Exit Function     End If      ColumnLetter   ThisWorkbook Sheets 1  Cells 1  Col Index  Address      Address in  A 1 format     ColumnLetter   Mid ColumnLetter  2  InStr 2  ColumnLetter       - 2    Extracts just the letter      ColLetter   ColumnLetter End Sub   After you have the input in the format  A 1  use the Mid function  start at position 2 to account for the first    then you find where the second   appears in the string using InStr  and then subtract 2 off to account for that starting position   This gives you the benefit of being adaptable for the whole range of possible columns  Therefore  ColLetter 1  gives back  A   and  ColLetter 16384  gives back  XFD   which is the last possible column for my Excel version

User · Answer

what about just converting to the ascii number and using Chr   to convert back to a letter   col letter   Chr Selection Column   96

User · Answer

This formula will give the column based on a range  i e   A1   where range is a single cell   If a multi-cell range is given it will return the top-left cell   Note  both cell references must be the same   MID CELL  address  A1  2 SEARCH     CELL  address  A1  2 -2   How it works   CELL  property   range   returns a specific value of the range depending on the property used   In this case the cell address  The address property returns a value   col   row   i e  A1 -   A 1  The MID function parses out the column value between the   symbols

User · Answer

LATEST UPDATE  Please ignore the function below   SurasinTancharoen managed to alert me that it is broken at n   53  For those who are interested  here are other broken values just below n   200     Please use  brettdj function for all your needs  It even works for Microsoft Excel latest maximum number of columns limit  16384 should gives XFD    END OF UPDATE    The function below is provided by Microsoft   Function ConvertToLetter iCol As Integer  As String    Dim iAlpha As Integer    Dim iRemainder As Integer    iAlpha   Int iCol   27     iRemainder   iCol -  iAlpha   26     If iAlpha  gt  0 Then       ConvertToLetter   Chr iAlpha   64     End If    If iRemainder  gt  0 Then       ConvertToLetter   ConvertToLetter  amp  Chr iRemainder   64     End If End Function   Source  How to convert Excel column numbers into alphabetical characters  APPLIES TO   Microsoft Office Excel 2007 Microsoft Excel 2002 Standard Edition Microsoft Excel 2000 Standard Edition Microsoft Excel 97 Standard Edition

User · Answer

Something that works for me is   Cells Row Column  Address    This will return the  AE 1 format reference for you

User · Answer

This is a version of robartsd s answer  with the flavor of Jan Wijninckx s one line solution   using recursion instead of a loop    Public Function ColumnLetter Column As Integer  As String     If Column  lt  1 Then Exit Function     ColumnLetter   ColumnLetter Int  Column - 1    26    amp  Chr   Column - 1  Mod 26    Asc  A    End Function   I ve tested this with the following inputs    1     gt   A  26    gt   Z  27    gt   AA  51    gt   AY  702   gt   ZZ  703   gt   AAA   -1    gt     -234  gt

User · Answer

Here is a late answer  just for simplistic approach using Int   and If in case of 1-3 character columns   Function outColLetterFromNumber i As Integer  As String      If i  lt  27 Then        one-letter         col   Chr 64   i      ElseIf i  lt  677 Then   two-letter         col   Chr 64   Int i   26    amp  Chr 64   i -  Int i   26    26       Else                  three-letter         col   Chr 64   Int i   676    amp  Chr 64   Int i - Int i   676    676    26    amp  Chr 64   i -  Int i - Int i   676    676    26    26       End If      outColLetterFromNumber   col  End Function

User · Answer

And a solution using recursion   Function ColumnNumberToLetter iCol As Long  As String      Dim lAlpha As Long     Dim lRemainder As Long      If iCol  lt   26 Then         ColumnNumberToLetter   Chr iCol   64      Else         lRemainder   iCol Mod 26         lAlpha   Int iCol   26          If lRemainder   0 Then             lRemainder   26             lAlpha   lAlpha - 1         End If         ColumnNumberToLetter   ColumnNumberToLetter lAlpha   amp  Chr lRemainder   64      End If  End Function

User · Answer

Cap A is 65 so   MsgBox Chr ActiveCell Column   64   Found in  http   www vbaexpress com forum showthread php 6103-Solved-get-column-letter

User · Answer

robertsd s code is elegant  yet to make it future-proof  change the declaration of n to type long  In case you want a formula to avoid macro s  here is something that works up to column 702 inclusive   IF A1 gt 26 CHAR INT  A1-1  26  64      amp CHAR MOD A1-1 26  65    where A1 is the cell containing the column number to be converted to letters

User · Answer

There is a very simple way using Excel power  Use Range Cells Address property  this way   strCol   Cells 1  lngRow  Address xlRowRelative  xlColRelative    This will return the address of the desired column on row 1  Take it of the 1   strCol   Left strCol  len strCol  - 1    Note that it so fast and powerful that you can return column addresses that even exists   Substitute lngRow for the desired column number using Selection Column property

User · Answer

this is only for REFEDIT      generaly use uphere code shortly version    easy to be read and understood    it use poz of        Private Sub RefEdit1 Change        Me Label1 Caption   NOtoLETTER RefEdit1 Value    you may assign to a variable  var        End Sub  Function NOtoLETTER REFedit       Dim First As Long  Second As Long      First   InStr REFedit                        first poz of       Second   InStr First   1  REFedit            second poz of        NOtoLETTER   Mid REFedit  First   1  Second - First - 1     extract COLUMN LETTER  End Function

User · Answer

If you d rather not use a range object   Function ColumnLetter ColumnNumber As Long  As String     Dim n As Long     Dim c As Byte     Dim s As String      n   ColumnNumber     Do         c     n - 1  Mod 26          s   Chr c   65   amp  s         n    n - c    26     Loop While n  gt  0     ColumnLetter   s End Function

User · Answer

Function fColLetter iCol As Integer  As String   On Error GoTo errLabel   fColLetter   Split Columns lngCol  Address   False        1    Exit Function errLabel    fColLetter     ERR   End Function

User · Answer

Here  a simple function in Pascal  Delphi    function GetColLetterFromNum Sheet   Variant  Col   Integer    String  begin   Result    Sheet Columns Col  Address      from Col 100 -- gt    CV  CV    Result    Copy Result  2  Pos      Result  - 2   end

User · Answer

This will work regardless of what column inside your one code line for cell thats located in row X  in column Y   Mid Cells X Y  Address  2  instr 2 Cells X Y  Address     -2    If you have a cell with unique defined name  Cellname    Mid Cells 1 val range  Cellname   Column   Address  2  instr 2 Cells 1 val range  Cellname   Column   Address     -2

User · Answer

This function returns the column letter for a given column number   Function Col Letter lngCol As Long  As String     Dim vArr     vArr   Split Cells 1  lngCol  Address True  False            Col Letter   vArr 0  End Function   testing code for column 100  Sub Test       MsgBox Col Letter 100  End Sub

User · Answer

Sub GiveAddress       Dim Chara As String     Chara          Dim Num As Integer     Dim ColNum As Long     ColNum   InputBox  Input the column number        Do         If ColNum  lt  27 Then             Chara   Chr ColNum   64   amp  Chara             Exit Do         Else             Num   ColNum   26             If  Num   26   gt  ColNum Then Num   Num - 1             If  Num   26    ColNum Then Num     ColNum - 1    26  - 1             Chara   Chr  ColNum -  26   Num     64   amp  Chara             ColNum   Num         End If     Loop      MsgBox  Address is     amp  Chara  amp       End Sub

User · Answer

Here s another way            Sub find test2                alpha col    A B C D E F G H I J K L M N O P Q R S T U V W X W Z               MsgBox Split alpha col       ActiveCell Column - 1          End Sub

User · Answer

Easy way to get the column name  Sub column    cell cells 1 1  column   Replace cell Address False  False   cell Row      msgbox column  End Sub   I hope it helps

User · Answer

Furthering on brettdj answer  here is to make the input of column number optional  If the column number input is omitted  the function returns the column letter of the cell that calls to the function  I know this can also be achieved using merely ColumnLetter COLUMN     but i thought it d be nice if it can cleverly understand so   Public Function ColumnLetter Optional ColumnNumber As Long   0  As String     If ColumnNumber   0 Then         ColumnLetter   Split Application Caller Address True  False  xlA1        0      Else         ColumnLetter   Split Cells 1  ColumnNumber  Address True  False  xlA1        0      End If End Function   The trade off of this function is that it would be very very slightly slower than brettdj s answer because of the IF test  But this could be felt if the function is repeatedly used for very large amount of times

User · Answer

This is a function based on  DamienFennelly s answer above  If you give me a thumbs up  give him a thumbs up too   P  Function outColLetterFromNumber iCol as Integer  as String     sAddr   Cells 1  iCol  Address     aSplit   Split sAddr           outColLetterFromNumber   aSplit 1  End Function

User · Answer

Here is a simple one liner that can be used   ColumnLetter   Mid Cells Row  LastColA  Address  2  1    It will only work for a 1 letter column designation  but it is nice for simple cases   If you need it to work for exclusively 2 letter designations  then you could use the following   ColumnLetter   Mid Cells Row  LastColA  Address  2  2

[excel] Function to convert column number to letter?

Examples related to excel

Examples related to vba

Examples related to excel-udf