[SOLVED] Function to convert column number to letter?

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

For example, entering 100 should return `CV`.

@SandPiper 2018-12-27 23:51:48

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

@ashleedawg 2018-03-30 15:22:56

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

@Thom 2018-01-31 18:49:22

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:

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.

@Jan Wijninckx 2014-02-17 03:29:01

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.

@OSUZorba 2014-05-23 15:22:58

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.

@alexanderbird 2015-02-04 16:18:07

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=> ""
``````

@alexanderbird 2015-02-04 19:56:23

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

@brettdj 2012-10-09 09:44:55

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
``````

@Caltor 2015-02-18 12:21:02

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

@brettdj 2015-02-18 14:30:27

That is quite correct, but I thought it more readable to use several lines.

@Excel Hero 2015-08-30 21:09:24

Why bother with the Boolean params in this situation. You can do this:................................................... `v = Split(Cells(1, lngCol).Address, "\$")(1)`

@chris neilsen 2016-09-29 02:02:53

@MátéJuhász Not true. `Split` always returns a 0 based array, regardless of `Option Base`. Option Base specifies the default lower bound when its not otherwise specified

@Selkie 2017-11-29 20:52:08

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

@Steven M. Vascellaro 2017-12-05 19:43:02

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.

@BrettFromLA 2014-03-20 18:43:32

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
outColLetterFromNumber = aSplit(1)
End Function
``````

@Ioannis 2014-05-23 15:34:25

Good one, but how is it different from the accepted answer?

@BrettFromLA 2014-05-23 17:11:29

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

@Jordi 2017-09-08 11:00:21

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;
``````

@Krzysztof 2017-03-04 22:36:16

``````Function fColLetter(iCol As Integer) As String
On Error GoTo errLabel
Exit Function
errLabel:
fColLetter = "%ERR%"
End Function
``````

@beef_supreme 2016-07-15 15:41:06

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

col_letter = Chr(Selection.Column + 96)

@Máté Juhász 2016-08-22 07:24:18

it doesn't work for column CV in the question:(

@ib11 2016-05-28 21:56:30

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
``````

@PEDRO COUTO 2016-03-30 09:31:28

Cap A is 65 so:

`MsgBox Chr(ActiveCell.Column + 64)`

@Máté Juhász 2016-08-22 07:23:53

it doesn't work for column `CV` in the question:(

@Gabriel V 2016-03-19 17:02:41

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
``````

@Rosetta 2016-03-19 04:52:09

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.

@Chetan V. 2016-02-04 11:16:16

``````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
``````

@mtbink.com 2014-07-20 14:46:53

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
``````

APPLIES TO

• Microsoft Office Excel 2007
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition

@Azuvector 2014-10-18 00:39:12

For reference, this pukes with larger column sets as Chr() doesn't handle large numbers well.

@Vignesh Subramanian 2015-04-28 05:55:13

@Azuvector will this work for values less than 100?

@Surasin Tancharoen 2015-09-24 07:32:09

This has a bug. Try ConvertToLetter(53) which should have been 'BA' but it will be fail.

@mtbink.com 2015-09-26 16:56:24

@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"`

@ib11 2016-05-28 21:44:45

And where on Earth does this "divide by 27" comes from? Last I checked there are 26 letters. This is why this code breaks.

@Bhanu Sinha 2015-09-22 20:54:34

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

Example:
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

@Will Ediger 2015-02-12 18:07:37

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
``````

@Alistair Collins 2014-12-09 12:08:47

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
End Function
``````

@cristobal 2014-11-11 12:09:03

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

@Codeplayer 2014-11-05 17:30:41

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

@Syd B 2014-08-26 14:15:33

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

@robartsd 2013-03-12 16:37:04

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
``````

@brettdj 2014-02-07 23:46:00

Not clear why you posted a longer method with a loop on the basis of If you'd rather not use a range object:

@Blackhawk 2014-05-23 17:05:26

@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

@brettdj 2014-05-24 03:02:10

@blackhawk, fair point re the speed. -1 removed.

@Engineer Toast 2015-02-17 22:10:30

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.

@Jon Peltier 2015-09-10 11:27:23

You could always test the input column number: `IF ColumnNumber <= 16384 Then`

@brettdj 2015-10-08 05:28:06

`IF ColumnNumber <= Columns.Count` would be better to avoid assumptions around versions.

@Steven M. Vascellaro 2017-12-05 20:18:37

For VBS users: If you get an error `Expected ')'`, you will need to remove the `As <Type>` statements. (As Long, As Byte, As String, etc)

@Maury Markowitz 2017-12-13 16:19:00

Another reason to use this code is if you're not in VBA but in VB, .net, etc.

@Mike Powell 2014-07-28 14:17:37

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

}
``````

@Caltor 2015-02-18 11:57:55

No need to create a string listing the letters of the alphabet. ASCII have essentially done that for us.

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!

@Nikolay Ivanov 2013-11-27 10:31:57

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
``````

@David Krider 2014-07-25 14:50:14

Cut-and-paste perfect to convert numbers greater than 676. Thanks!

@Caltor 2015-02-18 12:01:22

The remainder can never be more than 26 so why not an integer rather than long?

@Excel Hero 2015-08-30 20:49:09

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

@Caltor 2015-09-01 15:26:45

@ExcelHero I didn't know that. Doesn't a Long take more memory than an Integer though?

@Excel Hero 2015-09-01 15:35:39

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

@Damian Fennelly 2013-11-21 21:00:41

Something that works for me is:

``````Cells(Row,Column).Address
``````

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

[SOLVED] Is it possible to force Excel recognize UTF-8 CSV files automatically?

• 2011-05-14 13:53:39
• Lyubomyr Shaydariv
• 421945 View
• 361 Score
• Tags:   excel csv utf-8

[SOLVED] Returning a column number from a table using the =COLUMN function

• 2018-07-10 14:48:19
• Geminiflipflop
• 463 View
• 0 Score
• Tags:   excel vba excel-vba

[SOLVED] Stop Excel from automatically converting certain text values to dates

• 2008-10-02 23:30:43
• user16324
• 427678 View
• 464 Score
• Tags:   excel csv import

[SOLVED] How to avoid using Select in Excel VBA

• 2012-05-23 05:57:58
• BiGXERO
• 226544 View
• 465 Score
• Tags:   excel vba

[SOLVED] How to convert a column number (eg. 127) into an excel column (eg. AA)

• 2008-10-08 06:55:01
• robertkroll
• 202480 View
• 427 Score
• Tags:   c# excel

[SOLVED] Converting Numbers to Excel Letter Column vb.net

• 2015-08-12 20:14:24
• bill
• 6619 View
• 5 Score
• Tags:   vb.net excel chr