[WayBack] worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User:
Here’s the best I’ve found so far:
=SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")
The part in the middle marked in bold is the only part that changes. In this example, it’s taking the current column and adding1
, so returningB
if it’s in columnA
andAA
if it’s in columnZ
.
It is related to the question and answer [WayBack] Formula to return just the Column Letter in excel – Super User:
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)
And it is part of a much more elaborate answer