189

Does anyone have an Excel VBA function which can return the column letter(s) from a number?

For example, entering 100 should return CV.

Improve this question
4
  • 4
    Check this question out: stackoverflow.com/questions/10106465/… Commented Oct 9, 2012 at 11:14
  • @FrancisDean that is the reverse of this question which is looking for the address from the number Commented Mar 29, 2015 at 2:19
  • 2
    @brettdj The answer linked shows both number to letter and letter to number. Commented Mar 30, 2015 at 9:43
  • 2
    @FrancisDean fair point, I looked at the question title in the link to rather than the accepted answer Commented Mar 30, 2015 at 10:15

29 Answers 29

Reset to default
258

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You can add the (0) to the end of the Split command if you want to save yourself a variable declaration and extra line of code. eg Col_letter = Split(Cells(1, lngCol).Address(True, False), "$")(0)
That is quite correct, but I thought it more readable to use several lines.
Why bother with the Boolean params in this situation. You can do this:................................................... v = Split(Cells(1, lngCol).Address, "$")(1)
While this is very old, I have a minor addition - checking first if the number is positive, since otherwise you run into errors. if lngcol <=0 then
When using VBS, remember that .Cells is a property of Excel, meaning you need to use <excel_object>.Cells(). Otherwise, you will get a type mismatch error.
108

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
Improve this answer

7 Comments

Not clear why you posted a longer method with a loop on the basis of If you'd rather not use a range object:
@brettdj I can imagine several reasons: 1) this method is around 6x faster by my testing 2) it doesn't require access to the Excel API 3) it presumably has a smaller memory footprint. EDIT: Also, I'm not sure why I commented on an answer over a year old :S
There's a drawback to the increased speed, though. Using the range object throws an error if you pass in an invalid column number. It works even if someone is still using Excel 2003. If you need that kind of exception, go with the range method. Otherwise, kudos to robartsd.
IF ColumnNumber <= Columns.Count would be better to avoid assumptions around versions.
Another reason to use this code is if you're not in VBA but in VB, .net, etc.
|
60

Something that works for me is:

Cells(Row,Column).Address

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

Improve this answer

2 Comments

This doesn't answer the question.
This is exactly the answer that solved my problem. I am using another language, but it put me in the right ball park. I looked at it as pseudo code and figured it out. Here is what I used: $ws.Columns[$lastCol].Rows[$lastRow].Address().Replace('$','')
57

I'm surprised nobody suggested: Columns(Column Index).Address

  • For example: MsgBox Columns( 9347 ).Address returns $MUM:$MUM.

To return ONLY the column letter(s): Split((Columns(Column Index).Address(,0)),":")(0)

  • For example: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) returns DAD.

  More Examples

Improve this answer

3 Comments

This one works for me in Office 365: string col = Worksheet.Columns[column].Address; return col.Substring(col.IndexOf(":") + 2); For some reason other expressions, such as Range = Worksheet.Cells[1,column], were throwing errors (either in the Cells call or when I tried to take the address, can't remember - sorry - which lines where throwing in which specific cases.)
Even shorter to return only the column letter(s): Split(Columns(idx).Address, "$")(2) - instead of Split((Columns(idx).Address(,0)),":")(0), where idx is a Long representing the Column Index.
UPvoted for the tool tip, and so this is informative without giving everything away...there is a tool tip here. And: it is a testament to Microsoft's aeternal quest for excellence that such a simple and necessary thing as getting a column letter(s) is so awkward to get and generates such a lengthy thread.
21

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.

Improve this answer

Comments

20

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
Improve this answer

5 Comments

Cut-and-paste perfect to convert numbers greater than 676. Thanks!
The remainder can never be more than 26 so why not an integer rather than long?
@Caltor Unless you have a special purpose for using an Integer, like calling an API that demands one for example, you should never choose an Integer over a Long. VBA is optimized for Longs. VBA processes Longs faster than Integers.
@ExcelHero I didn't know that. Doesn't a Long take more memory than an Integer though?
@Caltor Indeed a Long is 32 bits, while an Integer is 16. But that does not matter in modern computing. 25 years ago... it mattered a lot. But today (even 15 years ago) the difference is totally inconsequential.
12

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
Improve this answer

Comments

12

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=> ""
Improve this answer

2 Comments

I've just noticed that this is essentially the same as Nikolay Ivanov's solution, which makes mine a little less novel. I'll leave it up because it shows a slightly different approach for a few of the minutia
Good solution for two reasons: not using any object (such as range, cell, and so on); very compact definition.
10

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.

Improve this answer

1 Comment

As addition, here is the formula that converts a column letter to a column number in similar style.
8

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:

Certain values of

Please use @brettdj function for all your needs. It even works for Microsoft Excel latest maximum number of columns limit: 16384 should gives XFD

enter image description here

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
Improve this answer

4 Comments

For reference, this pukes with larger column sets as Chr() doesn't handle large numbers well.
This has a bug. Try ConvertToLetter(53) which should have been 'BA' but it will be fail.
@SurasinTancharoen Thank you very much for noting this flaw. I have never thought Microsoft would provide a broken function as they are the one who created Microsoft Excel themselves. I will abandon this function from now on and will use @brettdj function that even correct up to latest Microsoft Excel maximum number of column limit Col_Letter(16384) = "XFD"
And where on Earth does this "divide by 27" comes from? Last I checked there are 26 letters. This is why this code breaks.
5

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
Improve this answer

2 Comments

Good one, but how is it different from the accepted answer?
@loannis I based mine on DamianFennelly's answer, not the accepted one. But yeah, mine looks a lot like the accepted answer, except one line is broken into two to make it more readable.
3

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!

Improve this answer

Comments

2

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)
Improve this answer

Comments

2

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)
Improve this answer

Comments

2

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.

Improve this answer

Comments

1

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

Improve this answer

Comments

1

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
Improve this answer

Comments

1

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.

Improve this answer

Comments

1

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
Improve this answer

Comments

1 
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
Improve this answer

Comments

1

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;
Improve this answer

Comments

1

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.

Improve this answer

Comments

0 
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
Improve this answer

Comments

0

Solution
It has been a while for this topic, but most solutions here apply using objects in excel logic having the downside of the limitations within it, for a wider scope, I attach the one that I use that uses an algorithm using math and characters, so it simplifies the process of translating to other programming languages for example, plus the function is so much shorter.

Function ColLtr(ByVal iCol As Long) As String ' shg 2012 If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26) End Function

This is the original thread where I asked for this and got this neat function that is a must when doing this compute.

Improve this answer

Comments

0

An addition to this already long testamentary to the quality and ease-of-use of VBA. This thing rids you of the need to explicitly eliminate the $ and takes a cell OR column number as an arg:

Function getColumnLtr(col) As String 'Given a column number OR CELL, return the equivalent column letter: If IsObject(col) Then getColumnLtr = getColumnLtr(col.Column) Else getColumnLtr = Split(Columns(col).address(columnabsolute:=False), ":")(0) End If End Function

Edit: I built this because I was inserting expressions in a column, and needed to generate address like A1, A2, A3. I was constructing them with "A" & nRow, and I needed to get the "A". Way way easier to use cell.offset(nRow, 0).address(...)

Improve this answer

Comments

-1

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)

Improve this answer

Comments

-1

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
Improve this answer

Comments

-2

Cap A is 65 so:

MsgBox Chr(ActiveCell.Column + 64)

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

Improve this answer

Comments

-2

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

col_letter = Chr(Selection.Column + 96)

Improve this answer

1 Comment

For cases where no column exceeds 26 (column 'Z') the correct version of the code above should be: strColLetter = ChrW(colIndex + 64)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.