Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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)

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

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

Using Callouts

If you want to put comments in your document, you can use Word's built-in comment feature. Another way is to use callout ...

Discover More

Customizing a Toolbar

Toolbars make it easy to quickly access your most common commands. Excel allows you to customize your toolbars so that ...

Discover More

Pasting Multiple Paragraphs Into a Single Cell

Copying information from one program (such as Word) to another (such as Excel) is a common occurrence. If you want to ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (ribbon)

Deriving Antilogs

Creating math formulas is a particular strong point of Excel. Not all the functions that you may need are built directly ...

Discover More

Determining a Name for a Week Number

You could use Excel to collect data that is useful in your business. For instance, you might use it to collect ...

Discover More

Dealing with Long Formulas

If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to ...

Discover More
##### Subscribe

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

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}] (all 7 characters, in the sequence shown) 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 less than 7?

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
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
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
ElseIf IsNumeric(ref) Then
If ref = 0 Then
ColRef = "> zero"
ElseIf ref > 16384 Then
ColRef = "< 16385"
Else
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
ElseIf IsNumeric(ref) Then
If ref = 0 Then
ColRef = "> zero"
ElseIf ref > 16384 Then
ColRef = "< 16385"
Else
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
ElseIf IsNumeric(ref) And ref > MAXCOL Then
ColRef = CVErr(xlErrRef)
ElseIf IsNumeric(ref) Then
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
ElseIf IsNumeric(ref) Then
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
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), _
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
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
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
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.