2

This question already has an answer here:

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 !

marked as duplicate by Máté Juhász, Pimp Juice IT, Ben N, JakeGould, Josh Oct 17 '17 at 20:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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 '17 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. – Máté Juhász Oct 16 '17 at 9:22
  • if it was dupe of that question, he would have already solved it with his original formula – PeterH Oct 16 '17 at 9:22
  • so if there ARE answers to my question, why have you copied in a link that DOESNT answer my question ? – user775305 Oct 16 '17 at 9:23
  • 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
5

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

  • brilliant ! I did try using substitute but then couldn't remove the numbers, THANKS – user775305 Oct 16 '17 at 9:16
  • Why not just only use Row() or Column()? – LPChip Oct 16 '17 at 9:47
  • =COLUMN() only gives the column number, not the letter – PeterH Oct 16 '17 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 '17 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. – Engineer Toast Oct 16 '17 at 11:47