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.

Answer 1

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

Answer 2

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) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

Answer 3

Something that works for me is:

Cells(Row,Column).Address 

This will return the $AE$1 format reference for you.

Answer 4

• 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 .

---

  

---

Answer 5

And a solution using recursion:

Function ColumnNumberToLetter(iCol As Long) As String

    Dim lAlpha As Long
    Dim lRemainder As Long

    If iCol <= 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) & Chr(lRemainder + 64)
    End If

End Function

Answer 6

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.

Answer 7

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

Answer 8

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 < 1 Then Exit Function
    ColumnLetter = ColumnLetter(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function

I've tested this with the following inputs:

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""

Answer 9

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>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)

where A1 is the cell containing the column number to be converted to letters.

Answer 10

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 > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & 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

Answer 11

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

Answer 12

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!

Answer 13

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)

Answer 14

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)

Answer 15

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 =)

Answer 16

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

Answer 17

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.

Answer 18

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 < 27 Then       'one-letter
        col = Chr(64 + i)
    ElseIf i < 677 Then  'two-letter
        col = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
    Else                 'three-letter
        col = Chr(64 + Int(i / 676)) & Chr(64 + Int(i - Int(i / 676) * 676) / 26)) & Chr(64 + i - (Int(i - Int(i / 676) * 676) / 26) * 26))
    End If

    outColLetterFromNumber = col

End Function

Answer 19

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

Answer 20

Here, a simple function in Pascal (Delphi).

function GetColLetterFromNum(Sheet : Variant; Col : Integer) : String;
begin
  Result := Sheet.Columns[Col].Address;  // from Col=100 --> '$CV:$CV'
  Result := Copy(Result, 2, Pos(':', Result) - 2);
end;

Answer 21

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.

Answer 22

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 <= 0 Or Col_Index >= 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.

Answer 23

Sub GiveAddress()
    Dim Chara As String
    Chara = ""
    Dim Num As Integer
    Dim ColNum As Long
    ColNum = InputBox("Input the column number")

    Do
        If ColNum < 27 Then
            Chara = Chr(ColNum + 64) & Chara
            Exit Do
        Else
            Num = ColNum / 26
            If (Num * 26) > ColNum Then Num = Num - 1
            If (Num * 26) = ColNum Then Num = ((ColNum - 1) / 26) - 1
            Chara = Chr((ColNum - (26 * Num)) + 64) & Chara
            ColNum = Num
        End If
    Loop

    MsgBox "Address is '" & Chara & "'."
End Sub

Answer 24

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)

Answer 25

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

Answer 26

Cap A is 65 so:

MsgBox Chr(ActiveCell.Column + 64)

Found in: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter

Answer 27

what about just converting to the ascii number and using Chr() to convert back to a letter?

col_letter = Chr(Selection.Column + 96)

Answer 28

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

}