How to get the current column name in Excel?

Question

What is the function to get the current line number and the current column name for a cell in Excel?

Answer 1

You can use the ROW and COLUMN functions to do this. If you omit the argument for those formulas, the current cell is used.

For example, if you enter =ROW() in cell D8, the value returned is 8. If you enter =COLUMN() in the same cell, the value returned is 4.

If you want the column letter, you can use the CHAR function. Simply add 64 to the column number (64 being one character less then A), so in the previous example, if you set the cell's value to =CHAR(COLUMN()+64), the value returned would be D. If you wanted a cell's value to be the cell location itself, the complete formula would be =CHAR(COLUMN()+64) & ROW().

---

Just an FYI, I got 64 from an ASCII table. You could also use the CODE formula, so the updated formula using this would be =CHAR(COLUMN() + CODE("A") - 1). You have to subtract 1 since the minimum value of COLUMN is always 1, and then the minimum return value of the entire formula would be B.

---

Lastly, if you want this to work with two-letter columns, you need the following formula:

=IF(COLUMN()>26,CHAR(INT((COLUMN()-1)/26)+64) & CHAR(IF(MOD(COLUMN(),26)=0,1,MOD(COLUMN(),26))+64),CHAR(COLUMN()+64))&ROW()

I'm not sure if there is an easier way to do it or not, but I know that works from cell A1 to ZZ99 with no problems.

Answer 2

Another possible way would be to use something like this:

=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())

Where colid refers to a named range you would create elsewhere within the workbook comprising two adjacent columns with multiple rows: the first column containing the numbers 1 to n corresponding to the COLUMN() number, the second containing the letters A - ZZ, or however many column references you wish to accommodate. The ROW() is fine left as it is to return the Row number.

So if you were to copy the above string to cell A1 of 'MySheet2', it would evaluate as =MySheet1!A1, and return the value it found in the corresponding cell of MySheet1.

This would enable you, for example, to use MySheet1 as a working area, to delete and re-insert new data, whilst any formatting or calculations in MySheet2 that refer to those contents will continue to work correctly with the new datasets from the target tabbed worksheet.

Answer 3

I'm not sure VBA solutions are accepted here but this was my solution.
Put the following in a code module:

Function COLUMNLETTER(Optional rng As Range) As String
    'Returns the Column Letter of the top left cell in rng.

    If rng Is Nothing Then Set rng = Application.Caller
    COLUMNLETTER = Left(rng.Address(0, 0), IIf(rng.Column > 26, IIf(rng.Column > 702, 3, 2), 1))

End Function

Then put =COLUMNLETTER() in any cell and you will get the column letter. :-P
Put =COLUMNLETTER(B3) in any cell and you will get B.

This User Defined Function works great when creating generic formulas inside the INDIRECT function.