Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Alphabetic Column Designation.

Alphabetic Column Designation

by Allen Wyatt
(last updated May 18, 2019)

12

You can easily determine the numeric column of cell by using the COLUMN function. All you need to do is put a formula like this in a cell, and the result is a value where A=1, B=2, etc.:

=COLUMN()

What if you want an alphabetic value, rather than a numeric value? This can be done in any of several different ways. For instance, the following formula will work very nicely for the first 26 columns, A through Z:

=CHAR(COLUMN()+64)

This works because the letters A through Z use character codes 65 through 90. When COLUMN returns a value for columns A through Z (1 through 26), this can be added to 64 to get the letters of those columns, 65 through 90.

Of course, this solution won't work if you want to know the letter designations of columns beyond Z. Since a column in Excel can have up to three digits (Excel can use columns up through XFD), a different approach to finding the column letters is in order:

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

The ADDRESS function returns the address of a specific cell. In this case, it returns the address for the cell in the first row of the current column. Thus, if the formula is in cell BF27, it returns BF1. The formula uses the LEFT function to return the correct number of left-most characters in the address, minus the number 1 for the row.

An even shorter version of the formula relies upon the SUBSTITUTE function instead of the LEFT function:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

Of course, you can also use a macro-based solution, if you want to. The following macro will work with one, two, or three character columns:

Function AlphaCol(c As Range) As String
    Dim sTemp As String
    Dim ad1 As String

    ad1 = c.Address
    sTemp = Mid(ad1, InStr(ad1, "$") + 1, InStr(2, ad1, "$") - 2)
    AlphaCol = sTemp
End Function

The macro is a user-defined function, which means that you can use it in your worksheets by simply adding this to any cell:

=AlphaCol(J12)

The cell referenced in the function call is a cell (any cell) within the column whose letter you want to know. The function finds that address for that cell and strips out everything except the column designation. A text string is returned, consisting of the column designation.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9240) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Alphabetic Column Designation.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Putting Character Codes to Work

If you know the character codes for some characters of interest, you can use those codes to do lots of tasks. This tip ...

Discover More

Linking Text Boxes

Text boxes can be an integral part of designing your documents. It can be helpful in your designs if text can ...

Discover More

Automatically Placing Text in a Comment

Want to automatically move the contents of a cell into a comment for that cell? It's easy enough to do by using the macro ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Incrementing References by Multiples when Copying Formulas

You can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied ...

Discover More

Returning Zero When a Referenced Cell is Blank

Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...

Discover More

Tracing Errors

Sometimes it can be confusing to figure out the source of an error that is displayed in your worksheet. Excel provides a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 4?

2019-05-27 11:09:04

Willy Vanhaelen

Peter,
I must admit that in your previous version that line was indeed executed because you had a line "On Error Resume Next" (I didn't pay attention to it until now).
The code line: "ColRef = Range(ref & 1).Column", generates a vba error when >XFD, causing CoLRef to turn to zero. Then the macro resumes with the following line where you trapped the 0.

Your last macro though has still some weaknesses.
Row 8 in your picture is not correct.
The macro works only with upper case letters. With lower case it generates always an error.
The line "If ColRef = 0 Then ColRef = "<=XFD" 'CVErr(2023)" will never be executed because the error is now trapped by "ElseIf Application.IsText(ref) And Left(ref, 3) > MAXAddr Then".

In fact in my initial version (23th May) there is only one situation where the user could be confused and that's if the argument is > 16384. But I thought it is so obvious that it was not worth it to do something about that. And if that bothers you it can easily be fixed by replacing Cells(ref) by Cells(1,ref). The other wrong arguments (zero and > XFD) cause vba errors that excel displays as #VALUE!. So I stick to that version because it's simple.

BTW in your versions you mention: "Author: Willy Vanhaelen" but that is not entirely correct because the changes made are not mine.


2019-05-26 19:02:01

Peter Atherton

Willy

However you placed "If ColRef = 0 Then ColRef = CVErr(2023)" in the "Else section" but there it will never be executed because the macro will only get there if ref contains column letter(s).

Yes, that is why I put it in, if someone entered say 'XFE1' then the COLREF = 0. I still think that your original is better with that modification.

Function ColRef(Optional ref As Variant) As Variant
'Author: Willy Vanhealen
'Date: 23 May 2019
Const MAXCOL As Integer = 16384
Const MAXAddr As String = "XDF"
Application.Volatile

If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) And ref > MAXCOL Then
ColRef = "<= 16384" 'CVErr(xlErrRef)
ElseIf Application.IsText(ref) And Left(ref, 3) > MAXAddr Then
ColRef = "<= XFD"
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
If ColRef = 0 Then ColRef = "<=XFD" 'CVErr(2023)
End If
End Function

(see Figure 1 below)

Figure 1. COLREF Data


2019-05-25 13:36:15

Willy Vanhaelen

Here is the macro with indentation because it is easier to understand. BUT DON'T COPY IT (it contains non-breaking spaces):

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
   ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
   If ref = 0 Then
      ColRef = "> zero"
   ElseIf ref > 16384 Then
      ColRef = "< 16385"
   Else
      ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
   End If
Else
   ColRef = Range(ref & 1).Column
End If
End Function


2019-05-25 13:22:38

Willy Vanhaelen

Peter,
I though about that but didn't include it for clarity. But you are right, users with less experience could be misled.

However you placed "If ColRef = 0 Then ColRef = CVErr(2023)" in the "Else section" but there it will never be executed because the macro will only get there if ref contains column letter(s).

Here is my version. Instead of using xlErrRef, I give the user a meaningfull message so he knows exactly what he did wrong:

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
If ref = 0 Then
ColRef = "> zero"
ElseIf ref > 16384 Then
ColRef = "< 16385"
Else
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
End If
Else
ColRef = Range(ref & 1).Column
End If
End Function


2019-05-24 22:08:09

Peter Atherton

Willy

I've added a couple of lines for error (colummns > 16384)

Function ColRef(Optional ref As Variant) As Variant
'Author: Willy Vanhealen
'Date: 23 May 2019
Const MAXCOL As Integer = 16384
On Error Resume Next

If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) And ref > MAXCOL Then
ColRef = CVErr(xlErrRef)
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
If ColRef = 0 Then ColRef = CVErr(2023)
End If
End Function


2019-05-23 11:43:19

Peter Atherton

Willy
I thought that you might improve the macros. Excellent a usual!


2019-05-23 11:22:46

Willy Vanhaelen

Here is a UDF that does it all:

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
End If
End Function

Syntax:
=ColRef() returns the column letter(s) of the cell the formula is entered in.
=ColRef(column number) returns the column letter(s) corresponding with the number (i.e. 27 -> AA).
=ColRef("column letter(s)") returns the number of the column letter(s) with quotes (i.e. "AB" -> 28).
=ColRef(cell reference) returns whatever is entered in that cell (column letter(s) without quotes or column number).


2019-05-22 13:44:49

Willy Vanhaelen

@Peter Atherton
Here is an even slightly shorter version of your macro:

Function ColName(ByVal n) As String
ColName = Mid(Cells(n).Address, 2, Len(Cells(n).Address) - 3)
End Function

I use the Mid function so I don't need the Address parameters (False, False).
I replaced Cells(1, n) by Cells(n). The row argument is not required here because cells(n) points to the nth cell in the first row until 16384. 16385 will return A, the first column of row 2.


2019-05-20 19:13:38

Peter Atherton

The ColName UDF with no tmp
Function ColName(ByVal n) As String
ColName = Left(Cells(1, n).Address(False, False), _
Len(Cells(1, n).Address(False, False)) - 1)
End Function

Error on the ColNumber UDF

2nd Line should read char = Right(ref, 1)


2019-05-19 20:56:20

Peter Atherton

Here is the way I did it, you enter a number for the column

Function ColName(ByVal n) As String
Dim tmp As String
tmp = Cells(1, n).Address(False, False)
ColName = Left(tmp, Len(tmp) - 1)
End Function

=COLNAME(45) returns AS

Its corollary returns the column number

Function ColNumber(ByVal ref) As Variant
Dim char As String
char = Left(ref, 1)
On Error Resume Next
If Not IsNumeric(char + 0) Then
ref = ref & "1"
End If
ColNumber = Range(ref).Column
If ColNumber = 0 Then
MsgBox "Last column name is XDF" & vbLf _
& "Last column number 16384"
ColNumber = ""
End If
End Function

syntax; =COLNUMBER(cell reference)
=COLNUMBER(L1) returns 27 when l1 contains AA


2019-05-19 09:31:17

Willy Vanhaelen

In the above macro, I don't see the necessety to declare the Temp variable and then use it only once. This macro is shorter and works the same:

Function AlphaCol(c As Range) As String
Dim ad1 As String
ad1 = c.Address
AlphaCol = Mid(ad1, InStr(ad1, "$") + 1, InStr(2, ad1, "$") - 2)
End Function

Because InStr(ad1, "$") + 1 always returns 2, you can even reduce it to a one-liner that does the job equally well:

Function AlphaCol(c As Range) As String
AlphaCol = Mid(c.Address, 2, InStr(2, c.Address, "$") - 2)
End Function


2019-05-18 06:16:46

Alex Guzman

Thank you for the insightful tip, Allen!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.