Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.
by Allen Wyatt
(last updated December 23, 2013)
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.:
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:
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:
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:
Of course, you can also use a macro-based solution, if you want to. The follow 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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9240) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Alphabetic Column Designation.
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!
If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...Discover More
Need to get at the last value in a column, regardless of how many cells are used within that column? You can apply the ...Discover More
You can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.