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 !

  • 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 –  Oct 16 '17 at 09: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. – Máté Juhász Oct 16 '17 at 09:22
  • 1
    if it was dupe of that question, he would have already solved it with his original formula – PeterH Oct 16 '17 at 09:22
  • 1
    so if there ARE answers to my question, why have you copied in a link that DOESNT answer my question ? –  Oct 16 '17 at 09:23
  • 1
    It is absolutely a duplicate of the above question. There are exact answers in that thread to your question. – Josh Oct 17 '17 at 20:05
  • `=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")` - the most concise and reliable – ZygD Feb 26 '21 at 12:19
  • https://superuser.com/a/511989/74576 was really helpful in explaining how to get the column letter in Excel (also works in Google Sheets). – Ryan Feb 27 '21 at 14:19

1 Answers1

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

PeterH
  • 7,377
  • 20
  • 54
  • 82
  • brilliant ! I did try using substitute but then couldn't remove the numbers, THANKS –  Oct 16 '17 at 09:16
  • Why not just only use Row() or Column()? – LPChip Oct 16 '17 at 09:47
  • =COLUMN() only gives the column number, not the letter – PeterH Oct 16 '17 at 09: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 '17 at 11:33
  • i think using CELL("address") makes it volatile – PeterH Oct 16 '17 at 11:36
  • 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. – Engineer Toast Oct 16 '17 at 11:47
  • Since the first $ will always be the first character, you can simplify that MID function, right? =MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLU‌​MN()),2)-2) – Engineer Toast Oct 16 '17 at 11:48
  • updated my answer to reflect your simplified version @EngineerToast – PeterH Oct 16 '17 at 11:52