Alphabetic Column Designation

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 ad1 As String

    ad1 = c.Address
    AlphaCol = Mid(ad1, InStr(ad1, "$") + 1, InStr(2, ad1, "$") - 2)
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.

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