What is the function to get the current line number and the current column name for a cell in Excel?
You can use the ROW
and COLUMN
functions to do this. If you omit the argument for those formulas, the current cell is used. These can be directly used with the OFFSET
function, or any other function where you can specify both the row and column as numerical values.
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. I do not recommend the use of letters to represent the column, as things get tricky when passing into doubleletter column names (where just using numbers is more logical anyways).
Regardless, if you should still want to get the column letter, you can 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
.
However, this will not work with twoletter columns. In that case, you need the following formula to properly parse twoletter columns:
=IF(COLUMN()>26,IF(RIGHT(CHAR(IF(MOD(COLUMN()1,26)=0,1,MOD(COLUMN()1,26))+64),1)="Y",CHAR(INT((COLUMN()1)/26)+64) & "Z",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. However, this illustrates why it's best to avoid the use of letterbased column identifiers, and stick with pure numberbased formulas (e.g. using the column number instead of letter with OFFSET
).


1@Jader Dias that is why I recommend that you use
OFFSET
instead, which allows you to specify columns as numbers. Regardless, I updated the answer with a formula to extend it to work with twoletter columns. – Breakthrough Jul 28 '11 at 17:05 
2I don't know why this answer got so many votes when Scot's answer is SO much better, except that he did not explain it. No complex formula needed. I added a comment to explain why/how it works. – Gerhard Powell Aug 6 '13 at 16:33

This works until you move to AA etc then its not going to work. – krystan honour Sep 30 '14 at 15:45

@krystanhonour use the last formula from my answer in that case...
However, this will not work with twoletter columns. In that case, you need the following formula to properly parse twoletter columns:
– Breakthrough Oct 1 '14 at 9:50
Try the following function:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Explanation: ADDRESS(row_num, column_num, [abs_num])
. [abs_num] = 4
= relative address. That means there are no '$'s in the returned value. For Column 'AB', the ADDRESS
will return 'AB1'. The substitute remove the '1'.
Try this
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
This gives you exact column header, without any $ etc.
Type this into any cell:
Español:
=SI(ENTERO((COLUMNA()1)/26)=0;"";CAR((ENTERO(COLUMNA()1)/26)+64))&CAR(COLUMNA()(ENTERO((COLUMNA()1)/26)*26)+64)
English:
=IF(INT((COLUMN()1)/26)=0,"",CHAR((INT(COLUMN()1)/26)+64))&CHAR(COLUMN()(INT((COLUMN()1)/26)*26)+64)
You can replace column() by row number.
To get column name I used the following formulae.
For a particular cell:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
For the current cell:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Try this variation. It works on 3letter columns, and doesn't leave a "$" on the front end:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Slightly manual but less VBA and a simpler formula:
 In a row of Excel, e.g. cell A1, enter the column number
=column()
 In the row below, enter
=Address(1,A1)
 This will provide the result
$A$1
Once values are copied, find and replace $
and 1
with blank.
This will work as well
=MID(CELL("address"),2,FIND("$",CELL("address"),2)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 reinsert 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.
Solution for polish Excel version:
 for a particular cell:
=PODSTAW(PODSTAW(KOMÓRKA("adres";B1);"$" & WIERSZ();""); "$"; "")
 for the current cell:
=PODSTAW(PODSTAW(KOMÓRKA("adres");"$" & WIERSZ();""); "$"; "")
Here's a VBA, user defined formula, solution. It works with 1, 2, & 3 letter columns.
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
=COLUMNLETTER() in any cell will return the column letter of the cell.
=COLUMNLETTER(B3) in any cell will return B.
This User Defined Function works great when creating generic formulas inside the INDIRECT function.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)2)
The address formula works by returning the column and row name. The returned format will always be $(Column Letters)$(Row Numbers)
 e.g. $AA$2
or $XAA$243556
If we know that $ will always occur at the first character, we can use the first mid formula to start pulling characters after the first $ sign (i.e. the 2nd character).
Then, we find the next $ sign (as we know there will only be two) and we know how many characters there are between the first and the second dollar sign. The rest is simple subtraction.
Here's how you can find the column heading (i.e. the letter):
=RIGHT(LEFT(ADDRESS(1,COLUMN()),LEN(ADDRESS(1,COLUMN()))2),LEN(ADDRESS(1,COLUMN()))3)
The following formula works no matter where you put it (it will give you back the column name).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")
try this:
=IF(COLUMN()>26,CHAR(COLUMN()/26 +64),"") & CHAR(MOD(COLUMN()1,26)+65) & ROW()
this function effective until "ZY1" or column=701

4

get the current column name in Excel, such as "A1", "B1", ... even up such as "AA1", "BA1", "CA1", ... – ayus Nov 7 '13 at 2:21
=IF(COLUMN()>702,RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),4),3),IF(COLUMN()>26,RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),3),2),RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),2),1)))
protected by Mokubai♦ Sep 17 '14 at 11:38
Thank you for your interest in this question.
Because it has attracted lowquality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
LIN
andCOL
but the problem is they return numbers, and I need the column letter toINDIRECT
it. – Jader Dias Jul 28 '11 at 14:36OFFSET
function in conjunction with, or instead of, theINDIRECT
formula in that case. If you want to use strings, though, I also updated my answer. – Breakthrough Jul 28 '11 at 14:39