8

I am looking for a formula I can use in any cell that will return the column LETTER of the column the formula is in,

I can get the LETTER and ROW number using the below, but I cant get it without the ROW information

=ADDRESS(CELL("row"),CELL("col"))

I don't mind using vba to work it out if there isn't a formula that can achieve this.

NOT A DUPLICATE OF How to get the current column name in Excel? I only want the COLUMN and not the line number !

7
  • 1
    I want JUST the column letter, he wanted current line number and the current column name, so no its not a duplicate, please actually read both questions before making assumptions
    – user775305
    Oct 16, 2017 at 9:20
  • There are answers to that questions which actually gives what you need. If you think it's not relevant for your question, then please explain why solutions offered there aren't work for you. Oct 16, 2017 at 9:22
  • 1
    if it was dupe of that question, he would have already solved it with his original formula
    – PeterH
    Oct 16, 2017 at 9:22
  • 1
    so if there ARE answers to my question, why have you copied in a link that DOESNT answer my question ?
    – user775305
    Oct 16, 2017 at 9:23
  • 1
    It is absolutely a duplicate of the above question. There are exact answers in that thread to your question.
    – Josh
    Oct 17, 2017 at 20:05

1 Answer 1

12

FYI on your original formula you don't actually need to call the CELL formula to get row and column you can use:

=ADDRESS(ROW(),COLUMN())

Then as an extension of that you can use MID & SEARCH to find the $ and trim down the output so you are just left with the letter:

=MID(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1,SEARCH("$",ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1)-2)

edit You can even simplify this further:

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

As per comment from @engineertoast below

8
  • brilliant ! I did try using substitute but then couldn't remove the numbers, THANKS
    – user775305
    Oct 16, 2017 at 9:16
  • Why not just only use Row() or Column()?
    – LPChip
    Oct 16, 2017 at 9:47
  • =COLUMN() only gives the column number, not the letter
    – PeterH
    Oct 16, 2017 at 9:49
  • @EngineerToast hey i just tried your formula, and something strange is happening, i drag it across multiple columns and they all return the same value, try it out let me know what happens for you please
    – PeterH
    Oct 16, 2017 at 11:33
  • 1
    @User91504 You're exactly right. Using CELL makes it reference the active cell as of the last calculation which is supremely unhelpful in this application... Simplifying the MID function is still valid, though. To avoid confusion, I'll delete / repost that comment without the CELL suggestion. Oct 16, 2017 at 11:47