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?

I just found out the functions `LIN` and `COL` but the problem is they return numbers, and I need the column letter to `INDIRECT` it. — Jader Dias, Jul 28 '11 at 14:36
you can use the `OFFSET` function in conjunction with, or instead of, the `INDIRECT` formula in that case. If you want to use strings, though, I also updated my answer. — Breakthrough, Jul 28 '11 at 14:39
By the way, INDIRECT is a volatile function, so use it sparingly. It does sound like am OFFSET might be a better choice here if you are basing the reference you want on some calculation of position. — AdamV, Aug 05 '11 at 14:06

score 48 · Answer 1 · edited May 06 '16 at 07:57

48

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'.

edited May 06 '16 at 07:57

Kromster

191
2
14

answered Nov 28 '12 at 18:31

Scot Sutton

9
2
2

This is the most concise and reliable. It's even shorter than VBA counterparts when written in the form `[SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")]` – ZygD Feb 26 '21 at 12:15

Breakthrough · Accepted Answer · 2013-08-06T17:52:48.817

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 double-letter 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 two-letter columns. In that case, you need the following formula to properly parse two-letter 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 letter-based column identifiers, and stick with pure number-based formulas (e.g. using the column number instead of letter with OFFSET).

It will work only for the first 26 columns. But that will do. — Jader Dias, Jul 28 '11 at 16:53
@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 two-letter columns. — Breakthrough, Jul 28 '11 at 17:05
I 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 06 '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 two-letter columns. In that case, you need the following formula to properly parse two-letter columns:` — Breakthrough, Oct 01 '14 at 09:50

score 5 · Answer 3 · edited Oct 16 '12 at 10:50

5

Try this

=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")

This gives you exact column header, without any $ etc.

edited Oct 16 '12 at 10:50

Dave

25,297
10
57
69

answered Oct 16 '12 at 10:40

gaurav aggarwal

31
1
1

score 3 · Answer 4 · edited Sep 20 '12 at 06:39

3

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.

edited Sep 20 '12 at 06:39

Indrek

24,204
14
90
93

answered Sep 20 '12 at 00:42

sygn

21
1

score 2 · Answer 5 · edited Oct 12 '12 at 19:17

2

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 3-letter columns, and doesn't leave a "$" on the front end:

=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")

edited Oct 12 '12 at 19:17

Community

1

answered Sep 26 '12 at 10:28

Vikas Madaan

11
1

score 2 · Answer 6 · edited Oct 17 '12 at 13:39

2

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.

edited Oct 17 '12 at 13:39

Indrek

24,204
14
90
93

answered Oct 17 '12 at 13:29

Steve

21
1

score 2 · Answer 7 · edited Dec 14 '12 at 14:53

2

This will work as well

=MID(CELL("address"),2,FIND("$",CELL("address"),2)-2)

edited Dec 14 '12 at 14:53

slhck

223,558
70
607
592

answered Dec 14 '12 at 12:14

edl

11
1

Can you add some explanation to this. – ChrisF Dec 14 '12 at 13:57

score 1 · Answer 8 · edited Mar 18 '12 at 09:26

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.

score 1 · Answer 9 · answered Oct 28 '13 at 05:25

1

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();""); "$"; "")

answered Oct 28 '13 at 05:25

mla

1
2

Bacon Bits · Answer 10 · 2021-06-24T20:09:28.420

The name of the current cell's column is:

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

The ROW([reference]) function returns the row's cardinal number of the referenced cell. ROW() returns the cardinal number of the row of the current cell.

The COLUMN([reference]) function returns the column's cardinal number of the referenced cell. COLUMN() returns the cardinal number of the column of the current cell.

The ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) returns the address (column+row) of the given input. If [abs_num] is 4, then the function returns a fully relative address (no $). [a1] and [sheet_text] are optional parameters and the default behavior is correct.

So, ADDRESS(ROW(),COLUMN(),4) is the relative address of the current cell.

Now we just need to remove the row number. Well, we already have a function that returns the row number: ROW(). And the column name will not interfere with that because it's always letters. So we can use simple string substitution to remove the row number and replace it with an empty value:

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

You can replace ROW() with a static value like 1 in the function, but in my experience I often want to get the current address in the same spreadsheet where I want the column name, so I find this one more flexible.

GollyJer · Answer 11 · 2012-08-29T12:54:54.217

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.

score 0 · Answer 12 · edited Feb 18 '13 at 18:07

=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.

score 0 · Answer 13 · edited May 22 '13 at 15:02

0

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)

edited May 22 '13 at 15:02

slm

9,959
10
49
57

answered May 22 '13 at 14:10

Hermen

1

score 0 · Answer 14 · edited Jun 08 '13 at 18:52

0

try this:

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

edited Jun 08 '13 at 18:52

Excellll

12,627
11
51
78

answered Jun 08 '13 at 18:18

denz

1

score 0 · Answer 15 · edited May 07 '14 at 20:02

0

The following formula works no matter where you put it (it will give you back the column name).

=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")

edited May 07 '14 at 20:02

Excellll

12,627
11
51
78

answered May 07 '14 at 19:56

JorgeHernandez

1

ayus · Answer 16 · 2013-11-07T02:30:51.067

-1

try this:

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

this function effective until "ZY1" or column=701

edited Nov 07 '13 at 02:30

answered Nov 06 '13 at 14:00

ayus

1
1

4

Please expand this answer by explaining what these functions are. – Kevin Panko Nov 06 '13 at 15:04
get the current column name in Excel, such as "A1", "B1", ... even up such as "AA1", "BA1", "CA1", ... – ayus Nov 07 '13 at 02:21

score -1 · Answer 17 · edited Dec 31 '13 at 20:51

-1

=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)))

edited Dec 31 '13 at 20:51

Excellll

12,627
11
51
78

answered Dec 31 '13 at 20:49

mkltmsck

1

How to get the current column name in Excel?

17 Answers17

Linked

Related